Free Microsoft Excel 2013 Quick Reference

Update / Edit Data Via User Form

Hello All,

I have created, with the help of this site as Excel document that a user can use to enter information when they push a button and have that information entered in a data page. Becuase of lack of having access to Access this is my only solution. The second step of what I am trying to achieve is to have a second button that allows a user to update pre-existing data. My thought was that I could identify an entry by the Tax Id # I have them enter. I know how to bring in the data back into the user form (on a static basis), but is there a way to have the data be searched out retrieved and then updated and saved without creating duplication. I have attached the associated spreadsheet. Any positive input would be greatly appreciated.

Thank You Very much in advance,

bgelhausen


Post your answer or comment

comments powered by Disqus
Hi I am trying to create a simple userform where the user is allowed to enter a unique ID and then using it retrieve and populate other fields in userform with data, allow users to edit the data and click on Save to update the row of data in excel spreadsheet.

The code I am using is below but for some reason I am having no luck getting it to work. The userform is launched from a worksheet call 'BOW' whilst the data is in another spreadsheet called 'OCI'. Would very much appreciate any help on this.


	VB:
	
 
Dim NotNow As Boolean 
Private Sub cmdCancel_Click() 
     'Unload the userform
    Unload Me 
End Sub 
Private Sub UserForm2_Initialize() 
    UserForm2.cmbItemName.RowSource = "A2:O" & Cells(Rows.Count, "A").End(xlUp).Row 
End Sub 
 
Private Sub btnEdit_Click() 
    NotNow = True 
     
    n = Application.Match(Me.cmbItemName.Value, Range("OCI!A:A"), 0) 
     
     
    Cells(n, 1).Value = Me.taDateOfRequest.Text 
    Cells(n, 2).Value = Me.taRequestorName.Text 
    Cells(n, 3).Value = Me.taChangeType.Text 
     
    NotNow = False 
End Sub 
Private Sub btnSearch_Click() 
    Sheets("OCI").Activate 
    If NotNow Then Exit Sub 
    vrange = "A1:C" & Cells(Rows.Count, "A").End(xlUp).Row 
    taDateOfRequest.Text = Application.VLookup(cmbItemName.Value, Sheets("OCI").Range(vrange), 2, False) 
    taRequestorName.Text = Application.VLookup(cmbItemName.Value, Sheets("OCI").Range(vrange), 3, False) 
    taChangeType.Text = Application.VLookup(cmbItemName.Value, Sheets("OCI").Range(vrange), 4, False) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi again,

Ive got my form working ok however once the form is filled in and the user presses the button to write that data to the spreadsheet. Id like to make it so that they are able to edit the data without having to type in the spreadsheet. (ive found that the users random spellings wreak havoc with my formulas!).

Ideally id have it so that the user could select a line from the spreadsheet and hit an "edit record" button that would then open that line of data in the form that created it.

or,

If the edit button could open a form similar to the form found in the Data/Form menu but with drop down menus for them to select corrections from rather than have them type them themselves.

Are either of these options even possible?

Regards
Damian

Hello
I created a userform (Excel 2003) with with multiple txtbox, cmbbox and button. I am able to copy the data to excel sheet and view them back on the userform using button click next and button click previous. Then i added a search button with InputBox, I was succesfull to view the data on the userform via search button, but it's only showing one data. what I would like is to see all duplicate data on the userform thru button click next and previous.
My problem is everytime i save the edited data it's not overwritting the old one, it's looking for the next empty row cell. Any possible solution would be greatly appreciated. thank you in advance

PS
Just to let you know i have more 2500 data save in excel sheet and lots of them are duplicate Name.

Hi,

I have difficulties with entering data into a worksheet from a user form.

The example in the attached file illustrates this. When adjusting the value in the user form with the spin button it seems as the text box-value is treated as text?

When writing directly in the text box, lets say 16,3, it is saved in the cell K3 as text?

Any suggestions are wellcome!

Hi,

I have populated the excel data in a user form and have provided the option to edit as well but unable to update the same to the workbook.

any pointer on this will be help full.

Code for Displaying the Data in User Form.

Private Sub CommandButton1_Click() 'in a userform on click of a command button system will check the combox value and populate the values of that row in the text box.

Dim Find As String
Dim RS As Range
Set RS = Sheets("Metric Tracking Sheet").Range("A2:A55")
Dim C
Find = ComboBox1.Value
With rsearch
Set C = .Find(strFind, LookIn:=xlValues, MatchCase:=True)
C.Select

With Me
.TextBox1.Value = ActiveCell.Offset(0, 5).Value
.TextBox2.Value = ActiveCell.Offset(0, 6).Value
.TextBox3.Value = ActiveCell.Offset(0, 7).Value
.TextBox4.Value = ActiveCell.Offset(0, 8).Value
.TextBox5.Value = ActiveCell.Offset(0, 9).Value
.TextBox6.Value = ActiveCell.Offset(0, 10).Value
.TextBox7.Value = ActiveCell.Offset(0, 11).Value
.TextBox8.Value = ActiveCell.Offset(0, 12).Value
.TextBox9.Value = ActiveCell.Offset(0, 13).Value
.TextBox10.Value = ActiveCell.Offset(0, 14).Value
.TextBox11.Value = ActiveCell.Offset(0, 15).Value
.TextBox12.Value = ActiveCell.Offset(0, 16).Value

End With

End Sub

Hi All,

I have a Excel Spreadsheet which I have created a User Form for the data entry, I would also like to be able to view the Data through the form to, I have crerated the relevant Buttons (Without the macros) but I am stuck from here on, VB Coding is not my field and I get very confused with it. If you are able to help me I have attached a copy of the Databse.

I would very much appreciate anyones help quite urgently.

Thank you so much

Shazz
xx

*EDIT* - If anyone wants the attachment, please download from post #2

Hi all,

I have a user form, and I am trying to write some code that will make the "Y" and "N" values from SQL update the userform to either check the box if the value from SQL is "Y", and leave it unchecked if the value is "N". I tried doing it with an iif statement, and that did not work. Here is what I have so far.

Private Sub cboInquiryName_Change()
'This is where you would want to populate all of the other fields within the form
Dim myset As New ADODB.Recordset
sSQL = "SELECT Account_Number, Account_Type, Notes, State_National, GO_Revenue, Quantity_Min, Quantity_Max, Price_Min, Price_Max, Coupon_Min, Coupon_Max, Maturity_Min, Maturity_Max, Call_Date_Min, Call_Date_Max, YTW_Min, YTW_Max, YTM_Min, YTM_Max, yield_notes, SP_Underlying_Min, SP_Underlying_Max, Moodys_Underlying_Min, Moodys_Underlying_Max, SP_Min, SP_Max, Moodys_Min, Moodys_Max, Insurance, Enhancement, Enhancement_Notes, Essential, Higher_Education, Airport, Housing, Hospital, Tobacco, RAN, COP, Refunding, Tax_Status"
sSQL = sSQL & " FROM Fixed_Income_Inquiry WHERE "
sSQL = sSQL & "NTUserID='" & sUserID & "' AND Inquiry_Name ='" & cboInquiryName.Value & "'"
Set myset = GetMyset(sSQL, PRD_FIG_Report_Test)
If myset.EOF Then
' MsgBox "Nothing Returned"
Else
txtAccountNumber.Value = IIf(IsNull(myset!Account_Number), "", myset!Account_Number)
cboAccountType.Value = IIf(IsNull(myset!Account_Type), "", myset!Account_Type)
txtNotes.Value = IIf(IsNull(myset!Notes), "", myset!Notes)
cboStateNational.Value = IIf(IsNull(myset!State_National), "", myset!State_National)
cboGORevenue.Value = IIf(IsNull(myset!GO_Revenue), "", myset!GO_Revenue)
txtQtyMin.Value = IIf(IsNull(myset!Quantity_Min), "", myset!Quantity_Min)
txtQtyMax.Value = IIf(IsNull(myset!Quantity_Max), "", myset!Quantity_Max)
txtPriceMin.Value = IIf(IsNull(myset!Price_Min), "", myset!Price_Min)
txtPriceMax.Value = IIf(IsNull(myset!Price_Max), "", myset!Price_Max)
txtCouponMin.Value = IIf(IsNull(myset!Coupon_Min), "", myset!Coupon_Min)
txtCouponMax.Value = IIf(IsNull(myset!Coupon_Max), "", myset!Coupon_Max)
txtMaturityMin.Value = IIf(IsNull(myset!Maturity_Min), "", myset!Maturity_Min)
txtMaturityMax.Value = IIf(IsNull(myset!Maturity_Max), "", myset!Maturity_Max)
txtCallDateMin.Value = IIf(IsNull(myset!Call_Date_Min), "", myset!Call_Date_Min)
txtCallDateMax.Value = IIf(IsNull(myset!Call_Date_Max), "", myset!Call_Date_Max)
txtYTWMin.Value = IIf(IsNull(myset!YTW_Min), "", myset!YTW_Min)
txtYTWMax.Value = IIf(IsNull(myset!YTW_Max), "", myset!YTW_Max)
txtYTMMin.Value = IIf(IsNull(myset!YTM_Min), "", myset!YTM_Min)
txtYTMMax.Value = IIf(IsNull(myset!YTM_Max), "", myset!YTM_Max)
txtYieldNotes.Value = IIf(IsNull(myset!Yield_Notes), "", myset!Yield_Notes)
cboUnderlyingSPMin.Value = IIf(IsNull(myset!SP_Underlying_Min), "", myset!SP_Underlying_Min)
cboUnderlyingSPMax.Value = IIf(IsNull(myset!SP_Underlying_Max), "", myset!SP_Underlying_Max)
cboUnderlyingMoodyMin.Value = IIf(IsNull(myset!Moodys_Underlying_Min), "", myset!Moodys_Underlying_Min)
cboUnderlyingMoodyMax.Value = IIf(IsNull(myset!Moodys_Underlying_Max), "", myset!Moodys_Underlying_Max)
cboSPMin.Value = IIf(IsNull(myset!SP_Min), "", myset!SP_Min)
cboSPMax.Value = IIf(IsNull(myset!SP_Max), "", myset!SP_Max)
cboMoodysMin.Value = IIf(IsNull(myset!Moodys_Min), "", myset!Moodys_Min)
cboMoodysMax.Value = IIf(IsNull(myset!Moodys_Max), "", myset!Moodys_Max)
cboInsurance.Value = IIf(IsNull(myset!Insurance), "", myset!Insurance)
cboEnhancement.Value = IIf(IsNull(myset!Enhancement), "", myset!Enhancement)
txtEnhancement.Value = IIf(IsNull(myset!Enhancement_Notes), "", myset!Enhancement_Notes)
chkEssential.Value = IIf(myset!Essential.Value = Y, True, False)
chkHigherEducation.Value = IIf(myset!Higher_Education.Value = Y, "TRUE", "FALSE")
chkAirport.Value = IIf(myset!Airport.Value = Y, True, False)
chkHousing.Value = IIf(myset!Housing.Value = Y, "TRUE", "FALSE")
chkHospital.Value = IIf(myset!Hospital.Value = Y, "TRUE", "FALSE")
chkTobacco.Value = IIf(myset!Tobacco.Value = Y, "TRUE", "FALSE")
chkRAN.Value = IIf(myset!RAN.Value = Y, "TRUE", "FALSE")
chkCOP.Value = IIf(myset!COP.Value = Y, "TRUE", "FALSE")
cboRefunding.Value = IIf(IsNull(myset!Refunding), "", myset!Refunding)
cboTaxStatus.Value = IIf(IsNull(myset!Tax_Status), "", myset!Tax_Status)
End If
End Sub

Thanks,

Hi all,

I have a user form, and I am trying to write some code that will make the "Y" and "N" values from SQL update the userform to either check the box if the value from SQL is "Y", and leave it unchecked if the value is "N". I tried doing it with an iif statement, and that did not work. Here is what I have so far.


	VB:
	
 cboInquiryName_Change() 
     'This is where you would want to populate all of the other fields within the form
    Dim myset As New ADODB.Recordset 
    sSQL = "SELECT Account_Number, Account_Type, Notes, State_National, GO_Revenue, Quantity_Min, Quantity_Max, Price_Min,
Price_Max, Coupon_Min, Coupon_Max, Maturity_Min, Maturity_Max, Call_Date_Min, Call_Date_Max, YTW_Min, YTW_Max, YTM_Min,
YTM_Max, yield_notes, SP_Underlying_Min, SP_Underlying_Max, Moodys_Underlying_Min, Moodys_Underlying_Max, SP_Min, SP_Max,
Moodys_Min, Moodys_Max, Insurance, Enhancement, Enhancement_Notes, Essential, Higher_Education, Airport, Housing, Hospital,
Tobacco, RAN, COP, Refunding, Tax_Status" 
    sSQL = sSQL & " FROM Fixed_Income_Inquiry WHERE " 
    sSQL = sSQL & "NTUserID='" & sUserID & "' AND Inquiry_Name ='" & cboInquiryName.Value & "'" 
    Set myset = GetMyset(sSQL, PRD_FIG_Report_Test) 
    If myset.EOF Then 
         ' MsgBox "Nothing Returned"
    Else 
        txtAccountNumber.Value = IIf(IsNull(myset!Account_Number), "", myset!Account_Number) 
        cboAccountType.Value = IIf(IsNull(myset!Account_Type), "", myset!Account_Type) 
        txtNotes.Value = IIf(IsNull(myset!Notes), "", myset!Notes) 
        cboStateNational.Value = IIf(IsNull(myset!State_National), "", myset!State_National) 
        cboGORevenue.Value = IIf(IsNull(myset!GO_Revenue), "", myset!GO_Revenue) 
        txtQtyMin.Value = IIf(IsNull(myset!Quantity_Min), "", myset!Quantity_Min) 
        txtQtyMax.Value = IIf(IsNull(myset!Quantity_Max), "", myset!Quantity_Max) 
        txtPriceMin.Value = IIf(IsNull(myset!Price_Min), "", myset!Price_Min) 
        txtPriceMax.Value = IIf(IsNull(myset!Price_Max), "", myset!Price_Max) 
        txtCouponMin.Value = IIf(IsNull(myset!Coupon_Min), "", myset!Coupon_Min) 
        txtCouponMax.Value = IIf(IsNull(myset!Coupon_Max), "", myset!Coupon_Max) 
        txtMaturityMin.Value = IIf(IsNull(myset!Maturity_Min), "", myset!Maturity_Min) 
        txtMaturityMax.Value = IIf(IsNull(myset!Maturity_Max), "", myset!Maturity_Max) 
        txtCallDateMin.Value = IIf(IsNull(myset!Call_Date_Min), "", myset!Call_Date_Min) 
        txtCallDateMax.Value = IIf(IsNull(myset!Call_Date_Max), "", myset!Call_Date_Max) 
        txtYTWMin.Value = IIf(IsNull(myset!YTW_Min), "", myset!YTW_Min) 
        txtYTWMax.Value = IIf(IsNull(myset!YTW_Max), "", myset!YTW_Max) 
        txtYTMMin.Value = IIf(IsNull(myset!YTM_Min), "", myset!YTM_Min) 
        txtYTMMax.Value = IIf(IsNull(myset!YTM_Max), "", myset!YTM_Max) 
        txtYieldNotes.Value = IIf(IsNull(myset!Yield_Notes), "", myset!Yield_Notes) 
        cboUnderlyingSPMin.Value = IIf(IsNull(myset!SP_Underlying_Min), "", myset!SP_Underlying_Min) 
        cboUnderlyingSPMax.Value = IIf(IsNull(myset!SP_Underlying_Max), "", myset!SP_Underlying_Max) 
        cboUnderlyingMoodyMin.Value = IIf(IsNull(myset!Moodys_Underlying_Min), "", myset!Moodys_Underlying_Min) 
        cboUnderlyingMoodyMax.Value = IIf(IsNull(myset!Moodys_Underlying_Max), "", myset!Moodys_Underlying_Max) 
        cboSPMin.Value = IIf(IsNull(myset!SP_Min), "", myset!SP_Min) 
        cboSPMax.Value = IIf(IsNull(myset!SP_Max), "", myset!SP_Max) 
        cboMoodysMin.Value = IIf(IsNull(myset!Moodys_Min), "", myset!Moodys_Min) 
        cboMoodysMax.Value = IIf(IsNull(myset!Moodys_Max), "", myset!Moodys_Max) 
        cboInsurance.Value = IIf(IsNull(myset!Insurance), "", myset!Insurance) 
        cboEnhancement.Value = IIf(IsNull(myset!Enhancement), "", myset!Enhancement) 
        txtEnhancement.Value = IIf(IsNull(myset!Enhancement_Notes), "", myset!Enhancement_Notes) 
        [B]chkEssential.Value = IIf(myset!Essential.Value = Y, True, False) 
        chkHigherEducation.Value = IIf(myset!Higher_Education.Value = Y, "TRUE", "FALSE") 
        chkAirport.Value = IIf(myset!Airport.Value = Y, True, False) 
        chkHousing.Value = IIf(myset!Housing.Value = Y, "TRUE", "FALSE") 
        chkHospital.Value = IIf(myset!Hospital.Value = Y, "TRUE", "FALSE") 
        chkTobacco.Value = IIf(myset!Tobacco.Value = Y, "TRUE", "FALSE") 
        chkRAN.Value = IIf(myset!RAN.Value = Y, "TRUE", "FALSE") 
        chkCOP.Value = IIf(myset!COP.Value = Y, "TRUE", "FALSE") 
        [/B]cboRefunding.Value = IIf(IsNull(myset!Refunding), "", myset!Refunding) 
        cboTaxStatus.Value = IIf(IsNull(myset!Tax_Status), "", myset!Tax_Status) 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you,

When entering data using a text box via a user form into an excel sheet is it possible have each new entry continued on the next cell down i.e. the cell which the text box is linked to will change to cell below automatically when some data is entered.

Hi there,

I'm building a large spreadsheet (an 8-week intraday shift planner...) and the macros take a while to process. In order to track the progress we've included a text box to update the status in the "mission control" user form (from which all processes are accessed).

We've included an instruction in the code to change the value of the Control Source cell to read "Processing Week One..." then "Processing Week Two..." etc.

The data in the cell *does* update, but the value in the User Form does not. It updates with the week 1 message (at the start of the process) and the week 8 message at the very end, but doesn't update in between.

Does anyone know of a way to force the value of the box in the user form to update?

Thanks in advance for any and all advice!

Andy

I have created a simple userform to add data to an Excel 2010 worksheet; the form contains two buttons, one to add new rows of data and the other to close the form. The worksheet has been populated with data by users using the userform and now I'd like to add functionality to allow the users to search for data, and to edit data that was found during their search.

Thanks

Tobie

Not sure if I am linking this correctly but here it goes...http://www.excelforum.com/excel-prog...ification.html

This is a previous post link that is now solved but I have a new question based on the code in the post. I would like to know how to add a search and update ability to the user form I have created. I am newb with user forms so I apologize in advance for my lack of knowledge.

And yes I know the way it is set up now it is depositing data in two different locations; this is for a specific purpose.

I have created a User Form in which data is entered by a user and put into a spreadsheet. The first entry from the User Form is a serial number. These serial numbers will not necessarily be entered into the spreadsheet in numerical order. How can I make sure that they get put in order after being added to the spreadsheet?

Following on from the suggestions made by a couple of people I have almost completed a custom search screen. The spreadsheet is attached, the final problem I am having is:

When you click on the FIND COST button a user form appears showing all the costs within the range "O:AN" for that row, the values in the cells are displayed in each txt box (this is what I want), however, if I decide to change a figure within a txt box and click on UPDATE the new value from the txt box does populate the correct cell HOWEVER the formatting disappears (its no longer treated as a currency or a number), therefore the sum formula in cell M5 DOES NOT UPDATE to show the new figures. It is critical that this cell updates because the value in this cell feeds the Cost Summary sheet.

Does anyone know how I can make the values contained in the text boxes when updated to be recognised as a number so that the formulas update automatically back on the sheet itself?

Any help would be GREATLY appreciated

Sir,
I need the following helps in preparing a user form controlled work book with multiple variables.

(a) I have created a user form with combo boxes and text boxes as inputs written to a defined worksheet in the work book. This is covering aound 25 columns when the data entered to the user form once and covering the defined row as it supposed to be. How the data could be written to another work sheet simultaneously in the same pattern?

(b) Secondly, how a simultaneous calculation (Sum function) can be done on the work sheet for each time data entered (need to add values of five inputs fed from the user form) and written to next coloumn as specified. For example If my data input is 05 different time durations entered as in put from the User form and are written to the specified worksheet in Cells D3 to H3. Now as soon as the data written on the columns, I need to fill the Cell I3 or any other selected cell in the same row automatically with the total sum of the cell values of D3 to H3. Can it be done with the VBA codes?

hi,

i got this program. Eg. in a worksheet, there's a column which is badge number. when i enter data through user form, if i gt the same badge number being entered, how do i check from the worksheet from that particular column that the badge number has already exists?

Hello guys,

Here to ask for a simple code on how can i display information on a user form once entered from an input box.

Example

A B C
1 E12858 Jorge Stregan
2 E112859 Rose Ann

Result:

Display all data in a row in userform once any data in A1 entered in input box.

Thanks.

I have managed to write some code to allow me to enter data via a form, but I would like to be able to update existing records and have the text boxes synchronise to the data sheet if the name is picked out from the combo.

Not sure if I should use a combo or list box. I have attached the file.

Thanks

Vetequk

Hi Guys,

I'm new here and need some help if possible. I am creating a huge spreedsheet in excel which involves the inputing of data from three operators in remote locations. I would like to use a user form as an interface for the people at the remote locations and I have created the user form to include labels and text boxes for the data to be entered into. My problem is, I want to transfer the entered data onto the next row of a sheet and then clear the form ready for the next set of data. I hope that I have explained this correctly.

Thanks in advance

In the attached example I can't add more than one name at a time without closing the user form. I can edit the list and click the "Save Changes" button and the changes happen. But using the "Add Name" button I can not add more than one name without closing the form, it just overwrites the previous name unless I close the form.

Thanks

Jim O

Hi,

I trying to create a user form whereby, the user inputs data in an Excel form and when clicking the Add button, the data in the form is stored in a worksheet.

I have the following code (tried to adapt it from an online source) however it is not working... once i click on the command button nothing happens!

Here is the code:

------------------------------------------

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Set ws = Worksheets("DataSource")


'find 1st empty row in database
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(irow + 1, 1) = TextBox1.Value
ws.Cells(irow + 1, 2) = TextBox2.Value

End Sub
------------------------------------------

Can anyone help please?

regards,

Is there an easy way to setup a user form in excel 2007 ?
Ideally the column headers would serve as a table or data list,which would then become the fields in the form.
The primary purpose is to have the form used for data entry & also act as a basis for searching the records.
Once the data has been entered into a new form this would have to be saved into the data sheet on the next available vacant row.
Can this be acheived in excel or am I just wishful thinking ? any help would be appreciated.

Hi All,

I am trying to create a VBA code in excel. When i select any row which i need to modify via user form and click Edit button then all the contents of the selected row should be displayed on USER form.

Could you please help on this how to implement and if you have any existing code related to this.

Thanks
Angeld27

Hello... new here and I hope this isn't too brain-dead a question. I have some VB code where a user form is used as a 'dashboard' to allow the user to select from a series of buttons, which takes them to various data input user forms. When the input is complete, the dashboard is displayed again.

On the dashboard I have summary totals of input done so far. My problem is that the dashboard does not update when new totals have been entered. I believe the reason for this is because the updating only happens when the dashboard user form is initialized. Currently, when I switch user forms, I Hide the dashboard user form. I think what I need to do is Unload it, then Load it again when the input is complete, so that it will re-initialize.

I can't seemto find the correct syntax to make this happen. Any suggestions?


No luck finding an answer? You could always try Google.