Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA userform troubles... adding to database no problem....updating modifying info = :(
only started to learn / use VBA code.
working on a userform for a data entry project. the data from the userform gets updated onto a database contained within the same worksheet (though hidden). here is my code: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for Triage Time In If Trim(Me.TextBox21.Value) = "" Then Me.TextBox21.SetFocus MsgBox "Please Enter Information into Triage Time In" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.TextBox21.Value ws.Cells(iRow, 2).Value = Me.ComboBox15.Value ws.Cells(iRow, 5).Value = Me.ComboBox12.Value ws.Cells(iRow, 6).Value = Me.TextBox36.Value ws.Cells(iRow, 7).Value = Me.TextBox35.Value ws.Cells(iRow, 8).Value = Me.TextBox34.Value ws.Cells(iRow, 9).Value = Me.TextBox33.Value ws.Cells(iRow, 10).Value = Me.ComboBox13.Value ws.Cells(iRow, 11).Value = Me.ComboBox11.Value ws.Cells(iRow, 12).Value = Me.ComboBox23.Value ws.Cells(iRow, 13).Value = Me.ComboBox21.Value ws.Cells(iRow, 14).Value = Me.ComboBox14.Value ws.Cells(iRow, 15).Value = Me.TextBox20.Value ws.Cells(iRow, 16).Value = Me.ComboBox19.Value ws.Cells(iRow, 17).Value = Me.ComboBox22.Value ws.Cells(iRow, 18).Value = Me.ComboBox16.Value ws.Cells(iRow, 19).Value = Me.TextBox18.Value ws.Cells(iRow, 20).Value = Me.ComboBox17.Value ws.Cells(iRow, 21).Value = Me.ComboBox24.Value ws.Cells(iRow, 22).Value = Me.ComboBox25.Value ws.Cells(iRow, 23).Value = Me.TextBox17.Value ws.Cells(iRow, 24).Value = Me.TextBox39.Value ws.Cells(iRow, 25).Value = Me.TextBox19.Value ws.Cells(iRow, 26).Value = Me.ComboBox18.Value ws.Cells(iRow, 3).Value = Me.ComboBox20.Value ws.Cells(iRow, 27).Value = Me.TextBox15.Value ws.Cells(iRow, 28).Value = Me.ComboBox10.Value ws.Cells(iRow, 29).Value = Me.TextBox26.Value ws.Cells(iRow, 30).Value = Me.TextBox28.Value ws.Cells(iRow, 49).Value = Me.TextBox40.Value 'clear the data Me.ComboBox14.Value = "" Me.TextBox21.Value = "" Me.ComboBox15.Value = "" Me.ComboBox12.Value = "" Me.TextBox36.Value = "" Me.TextBox35.Value = "" Me.TextBox34.Value = "" Me.TextBox33.Value = "" Me.ComboBox13.Value = "" Me.ComboBox11.Value = "" Me.ComboBox23.Value = "" Me.ComboBox21.Value = "" Me.TextBox20.Value = "" Me.ComboBox19.Value = "" Me.ComboBox22.Value = "" Me.ComboBox16.Value = "" Me.TextBox18.Value = "" Me.ComboBox17.Value = "" Me.ComboBox24.Value = "" Me.ComboBox25.Value = "" Me.TextBox17.Value = "" Me.TextBox39.Value = "" Me.TextBox19.Value = "" Me.ComboBox18.Value = "" Me.ComboBox20.Value = "" Me.TextBox15.Value = "" Me.ComboBox10.Value = "" Me.TextBox26.Value = "" Me.TextBox28.Value = "" Me.TextBox40.Value = "" Me.ComboBox14.SetFocus End Sub Private Sub cmdClear_Click_Click() Me.ComboBox14.Value = Null Me.TextBox21.Value = Null Me.ComboBox15.Value = Null Me.ComboBox12.Value = Null Me.TextBox36.Value = Null Me.TextBox35.Value = Null Me.TextBox34.Value = Null Me.TextBox33.Value = Null Me.ComboBox13.Value = Null Me.ComboBox11.Value = Null Me.ComboBox23.Value = Null Me.ComboBox21.Value = Null Me.TextBox20.Value = Null Me.ComboBox19.Value = Null Me.ComboBox22.Value = Null Me.ComboBox16.Value = Null Me.TextBox18.Value = Null Me.ComboBox17.Value = Null Me.ComboBox24.Value = Null Me.ComboBox25.Value = Null Me.TextBox17.Value = Null Me.TextBox39.Value = Null Me.TextBox19.Value = Null Me.ComboBox18.Value = Null Me.ComboBox20.Value = Null Me.TextBox15.Value = Null Me.ComboBox10.Value = Null Me.TextBox26.Value = Null Me.TextBox28.Value = Null Me.TextBox40.Value = Null End Sub Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True MsgBox "To ensure you want to terminate this Program Please use CLOSE tab located below" End If End Sub Private Sub cmdClose_Click() Unload Me End Sub Code: -------------------------- very messing but does what i need it to at the moment. i have 3 command tabs (update / close / clear all) i want to add another command tab to search the (hidden) database then populate the textbox's / combobox's if possible. then the user can back track through what has already been enter then make changes as and where needed. can anyone point me in the right direction or save me from myself :( any help will be greatly appreciated. thanks Andrew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA userform troubles... adding to database no problem....updating modifying info = :(
Maybe I'm not understanding the problem, but why not just use the reverse
procedure while showing the UserForm? Me.TextBox21.Value = ws.Cells(iRow, 1).Value Etc. __________________________________________________ ____________________ wrote in message ups.com... only started to learn / use VBA code. working on a userform for a data entry project. the data from the userform gets updated onto a database contained within the same worksheet (though hidden). here is my code: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for Triage Time In If Trim(Me.TextBox21.Value) = "" Then Me.TextBox21.SetFocus MsgBox "Please Enter Information into Triage Time In" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.TextBox21.Value ws.Cells(iRow, 2).Value = Me.ComboBox15.Value ws.Cells(iRow, 5).Value = Me.ComboBox12.Value ws.Cells(iRow, 6).Value = Me.TextBox36.Value ws.Cells(iRow, 7).Value = Me.TextBox35.Value ws.Cells(iRow, 8).Value = Me.TextBox34.Value ws.Cells(iRow, 9).Value = Me.TextBox33.Value ws.Cells(iRow, 10).Value = Me.ComboBox13.Value ws.Cells(iRow, 11).Value = Me.ComboBox11.Value ws.Cells(iRow, 12).Value = Me.ComboBox23.Value ws.Cells(iRow, 13).Value = Me.ComboBox21.Value ws.Cells(iRow, 14).Value = Me.ComboBox14.Value ws.Cells(iRow, 15).Value = Me.TextBox20.Value ws.Cells(iRow, 16).Value = Me.ComboBox19.Value ws.Cells(iRow, 17).Value = Me.ComboBox22.Value ws.Cells(iRow, 18).Value = Me.ComboBox16.Value ws.Cells(iRow, 19).Value = Me.TextBox18.Value ws.Cells(iRow, 20).Value = Me.ComboBox17.Value ws.Cells(iRow, 21).Value = Me.ComboBox24.Value ws.Cells(iRow, 22).Value = Me.ComboBox25.Value ws.Cells(iRow, 23).Value = Me.TextBox17.Value ws.Cells(iRow, 24).Value = Me.TextBox39.Value ws.Cells(iRow, 25).Value = Me.TextBox19.Value ws.Cells(iRow, 26).Value = Me.ComboBox18.Value ws.Cells(iRow, 3).Value = Me.ComboBox20.Value ws.Cells(iRow, 27).Value = Me.TextBox15.Value ws.Cells(iRow, 28).Value = Me.ComboBox10.Value ws.Cells(iRow, 29).Value = Me.TextBox26.Value ws.Cells(iRow, 30).Value = Me.TextBox28.Value ws.Cells(iRow, 49).Value = Me.TextBox40.Value 'clear the data Me.ComboBox14.Value = "" Me.TextBox21.Value = "" Me.ComboBox15.Value = "" Me.ComboBox12.Value = "" Me.TextBox36.Value = "" Me.TextBox35.Value = "" Me.TextBox34.Value = "" Me.TextBox33.Value = "" Me.ComboBox13.Value = "" Me.ComboBox11.Value = "" Me.ComboBox23.Value = "" Me.ComboBox21.Value = "" Me.TextBox20.Value = "" Me.ComboBox19.Value = "" Me.ComboBox22.Value = "" Me.ComboBox16.Value = "" Me.TextBox18.Value = "" Me.ComboBox17.Value = "" Me.ComboBox24.Value = "" Me.ComboBox25.Value = "" Me.TextBox17.Value = "" Me.TextBox39.Value = "" Me.TextBox19.Value = "" Me.ComboBox18.Value = "" Me.ComboBox20.Value = "" Me.TextBox15.Value = "" Me.ComboBox10.Value = "" Me.TextBox26.Value = "" Me.TextBox28.Value = "" Me.TextBox40.Value = "" Me.ComboBox14.SetFocus End Sub Private Sub cmdClear_Click_Click() Me.ComboBox14.Value = Null Me.TextBox21.Value = Null Me.ComboBox15.Value = Null Me.ComboBox12.Value = Null Me.TextBox36.Value = Null Me.TextBox35.Value = Null Me.TextBox34.Value = Null Me.TextBox33.Value = Null Me.ComboBox13.Value = Null Me.ComboBox11.Value = Null Me.ComboBox23.Value = Null Me.ComboBox21.Value = Null Me.TextBox20.Value = Null Me.ComboBox19.Value = Null Me.ComboBox22.Value = Null Me.ComboBox16.Value = Null Me.TextBox18.Value = Null Me.ComboBox17.Value = Null Me.ComboBox24.Value = Null Me.ComboBox25.Value = Null Me.TextBox17.Value = Null Me.TextBox39.Value = Null Me.TextBox19.Value = Null Me.ComboBox18.Value = Null Me.ComboBox20.Value = Null Me.TextBox15.Value = Null Me.ComboBox10.Value = Null Me.TextBox26.Value = Null Me.TextBox28.Value = Null Me.TextBox40.Value = Null End Sub Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True MsgBox "To ensure you want to terminate this Program Please use CLOSE tab located below" End If End Sub Private Sub cmdClose_Click() Unload Me End Sub Code: -------------------------- very messing but does what i need it to at the moment. i have 3 command tabs (update / close / clear all) i want to add another command tab to search the (hidden) database then populate the textbox's / combobox's if possible. then the user can back track through what has already been enter then make changes as and where needed. can anyone point me in the right direction or save me from myself :( any help will be greatly appreciated. thanks Andrew |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA userform troubles... adding to database no problem....updating modifying info = :(
i really dont know how to, to refer to the worksheet, cell range, then
to populate each field as required. and then to update the database without duplications. everything else i have managed to work out, but this seems alot tricker than expected. i can see what your saying, but im not sure i understand in the long run. i would need to search from say... Textbox35. being the only unique field i have (9 digit number).... then on the database there will be roughly 100 entries per week. say 400 per month. i need the find command to search through these entries. then to repopulate the userform (with the missing information still blanked) then the user can add the information as and where required then update this info to complete the database. again sorry for not making myself clear. any help will be appreciated. Andrew --------------------------------------------------------------------- On Jun 15, 6:18 pm, "Vasant Nanavati" <vasantn AT aol DOT com wrote: Maybe I'm not understanding the problem, but why not just use the reverse procedure while showing the UserForm? Me.TextBox21.Value = ws.Cells(iRow, 1).Value Etc. __________________________________________________ ____________________ wrote in message ups.com... only started to learn / use VBA code. working on a userform for a data entry project. the data from the userform gets updated onto a database contained within the same worksheet (though hidden). here is my code: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for Triage Time In If Trim(Me.TextBox21.Value) = "" Then Me.TextBox21.SetFocus MsgBox "Please Enter Information into Triage Time In" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.TextBox21.Value ws.Cells(iRow, 2).Value = Me.ComboBox15.Value ws.Cells(iRow, 5).Value = Me.ComboBox12.Value ws.Cells(iRow, 6).Value = Me.TextBox36.Value ws.Cells(iRow, 7).Value = Me.TextBox35.Value ws.Cells(iRow, 8).Value = Me.TextBox34.Value ws.Cells(iRow, 9).Value = Me.TextBox33.Value ws.Cells(iRow, 10).Value = Me.ComboBox13.Value ws.Cells(iRow, 11).Value = Me.ComboBox11.Value ws.Cells(iRow, 12).Value = Me.ComboBox23.Value ws.Cells(iRow, 13).Value = Me.ComboBox21.Value ws.Cells(iRow, 14).Value = Me.ComboBox14.Value ws.Cells(iRow, 15).Value = Me.TextBox20.Value ws.Cells(iRow, 16).Value = Me.ComboBox19.Value ws.Cells(iRow, 17).Value = Me.ComboBox22.Value ws.Cells(iRow, 18).Value = Me.ComboBox16.Value ws.Cells(iRow, 19).Value = Me.TextBox18.Value ws.Cells(iRow, 20).Value = Me.ComboBox17.Value ws.Cells(iRow, 21).Value = Me.ComboBox24.Value ws.Cells(iRow, 22).Value = Me.ComboBox25.Value ws.Cells(iRow, 23).Value = Me.TextBox17.Value ws.Cells(iRow, 24).Value = Me.TextBox39.Value ws.Cells(iRow, 25).Value = Me.TextBox19.Value ws.Cells(iRow, 26).Value = Me.ComboBox18.Value ws.Cells(iRow, 3).Value = Me.ComboBox20.Value ws.Cells(iRow, 27).Value = Me.TextBox15.Value ws.Cells(iRow, 28).Value = Me.ComboBox10.Value ws.Cells(iRow, 29).Value = Me.TextBox26.Value ws.Cells(iRow, 30).Value = Me.TextBox28.Value ws.Cells(iRow, 49).Value = Me.TextBox40.Value 'clear the data Me.ComboBox14.Value = "" Me.TextBox21.Value = "" Me.ComboBox15.Value = "" Me.ComboBox12.Value = "" Me.TextBox36.Value = "" Me.TextBox35.Value = "" Me.TextBox34.Value = "" Me.TextBox33.Value = "" Me.ComboBox13.Value = "" Me.ComboBox11.Value = "" Me.ComboBox23.Value = "" Me.ComboBox21.Value = "" Me.TextBox20.Value = "" Me.ComboBox19.Value = "" Me.ComboBox22.Value = "" Me.ComboBox16.Value = "" Me.TextBox18.Value = "" Me.ComboBox17.Value = "" Me.ComboBox24.Value = "" Me.ComboBox25.Value = "" Me.TextBox17.Value = "" Me.TextBox39.Value = "" Me.TextBox19.Value = "" Me.ComboBox18.Value = "" Me.ComboBox20.Value = "" Me.TextBox15.Value = "" Me.ComboBox10.Value = "" Me.TextBox26.Value = "" Me.TextBox28.Value = "" Me.TextBox40.Value = "" Me.ComboBox14.SetFocus End Sub Private Sub cmdClear_Click_Click() Me.ComboBox14.Value = Null Me.TextBox21.Value = Null Me.ComboBox15.Value = Null Me.ComboBox12.Value = Null Me.TextBox36.Value = Null Me.TextBox35.Value = Null Me.TextBox34.Value = Null Me.TextBox33.Value = Null Me.ComboBox13.Value = Null Me.ComboBox11.Value = Null Me.ComboBox23.Value = Null Me.ComboBox21.Value = Null Me.TextBox20.Value = Null Me.ComboBox19.Value = Null Me.ComboBox22.Value = Null Me.ComboBox16.Value = Null Me.TextBox18.Value = Null Me.ComboBox17.Value = Null Me.ComboBox24.Value = Null Me.ComboBox25.Value = Null Me.TextBox17.Value = Null Me.TextBox39.Value = Null Me.TextBox19.Value = Null Me.ComboBox18.Value = Null Me.ComboBox20.Value = Null Me.TextBox15.Value = Null Me.ComboBox10.Value = Null Me.TextBox26.Value = Null Me.TextBox28.Value = Null Me.TextBox40.Value = Null End Sub Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True MsgBox "To ensure you want to terminate this Program Please use CLOSE tab located below" End If End Sub Private Sub cmdClose_Click() Unload Me End Sub Code: -------------------------- very messing but does what i need it to at the moment. i have 3 command tabs (update / close / clear all) i want to add another command tab to search the (hidden) database then populate the textbox's / combobox's if possible. then the user can back track through what has already been enter then make changes as and where needed. can anyone point me in the right direction or save me from myself :( any help will be greatly appreciated. thanks Andrew- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
modifying code troubles | Excel Programming | |||
application userform database problem | Excel Programming | |||
Having troubles using visual basic to paste info from one workbook to another | Excel Programming | |||
Updating database worksheet problem (Template Wizard) | Excel Discussion (Misc queries) | |||
Userform troubles! | Excel Programming |