Modify a VBA Form

dosman

New Member
Joined
Jan 24, 2019
Messages
35
Greetings, Excel users!

I have Workbook with 2 sheets. Its is a combination of text fields, data validation drop lists, and protected formulas. A very helpful friend helped me with a VBA form to enter data into the main sheet. The second sheet only holds values for the data validation.

Over time it has become neccessary to change one of the form fields from a combobox to a text box and eliminate the data validation. I cleared the validation in the column I want to change but the form is giving me fits. I edited the form and deleted the combobox and replaced it with a textbox. When I attempt to edit the code I get in over my head due to my lack of knowledge of VBA. I understand it at a surface level at best. I am asking for guidance on how to alter the VBA code to match the changes.

I need to convert column J from combobox to textbox which seemed pretty straightforward until I realized that J is referenced in a Private Sub at the end of the code which is where the debugger keeps landing me.

This is the original code

VBA Code:
Dim sh1 As Worksheet, sh2 As Worksheet
Private Sub CommandButton1_Click()
    Dim lr As Long
    If TextBox1.Value = "" Or Not IsDate(TextBox1) Then
        MsgBox "Enter a date", vbExclamation
        TextBox1.SetFocus
        Exit Sub
    End If
    '
    lr = sh1.Range("A" & Rows.Count).End(xlUp)(2).Row
    sh1.Cells(lr, "A").Value = TextBox1.Value   'DATE
    sh1.Cells(lr, "B").Value = TextBox2.Value   'DATE SUB
    sh1.Cells(lr, "C").Value = TextBox3.Value   'JOB#
    sh1.Cells(lr, "D").Value = ComboBox1.Value  'AGENCY
    sh1.Cells(lr, "E").Value = TextBox4.Value   'JOB NOTES
    sh1.Cells(lr, "F").Value = ComboBox2.Value  'ORDER
    sh1.Cells(lr, "G").Value = ComboBox3.Value  'COPY PAY ST
    sh1.Cells(lr, "H").Value = ComboBox4.Value  'EXPECTED BY
    sh1.Cells(lr, "U").Value = ComboBox10.Value 'CANCELLATION
    If TextBox5.Value <> "" Then
        sh1.Cells(lr, "I").Value = CDbl(TextBox5.Value)   'PAGES
    End If
    [COLOR=rgb(235, 107, 86)]If ComboBox5.Value <> "" Then
        sh1.Cells(lr, "J").Value = CDbl(ComboBox5.Value)  'PAGE RATE[/COLOR]
    End If
    If ComboBox6.Value <> "" Then
        sh1.Cells(lr, "M").Value = CDbl(ComboBox6.Value)  'VIDEO
    End If
    If ComboBox7.Value <> "" Then
        sh1.Cells(lr, "N").Value = CDbl(ComboBox7.Value)  'ROUGH
    End If
    If ComboBox8.Value <> "" Then
        sh1.Cells(lr, "O").Value = CDbl(ComboBox8.Value)  'LIVENOTE
    End If
    If TextBox6.Value <> "" Then
        sh1.Cells(lr, "S").Value = CDbl(TextBox6.Value)   'OT/DT
    End If
    If TextBox7.Value <> "" Then
        sh1.Cells(lr, "T").Value = CDbl(TextBox7.Value)   'PARKING
    End If
    If TextBox8.Value <> "" Then
        sh1.Cells(lr, "W").Value = CDbl(TextBox8.Value)   'OTHER FEES
    End If
    If ComboBox9.Value <> "" Then
        sh1.Cells(lr, "X").Value = CDbl(ComboBox9.Value)   'PER DIEM
    End If
    If TextBox9.Value <> "" Then
        sh1.Cells(lr, "AA").Value = CDbl(TextBox9.Value)  'SCOPING FEES
    End If
        
    ComboBox1.ListIndex = -1
    ComboBox2.ListIndex = -1
    ComboBox3.ListIndex = -1
    ComboBox4.ListIndex = -1
    [COLOR=rgb(226, 80, 65)]ComboBox5.ListIndex = -1[/COLOR]
    ComboBox6.ListIndex = -1
    ComboBox7.ListIndex = -1
    ComboBox8.ListIndex = -1
    ComboBox9.ListIndex = -1
    ComboBox10.ListIndex = -1
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
End Sub

Private Sub Label19_Click()

End Sub

Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'PAGES
    If Not (KeyAscii >= 48 And KeyAscii <= 57) Then KeyAscii = 0
End Sub
Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'OT/DT
    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0
End Sub
Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'PARKING
    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0
End Sub
Private Sub TextBox8_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'OTHER FEES
    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0
End Sub
Private Sub TextBox9_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'SCOPING FEES
    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0
    End Sub
Private Sub UserForm_Activate()
    Set sh1 = Sheets("Receivables")
    Set sh2 = Sheets("Data")
    '
   [COLOR=rgb(235, 107, 86)] For j = 1 To Columns("J").Column
        For i = 2 To sh2.Cells(Rows.Count, j).End(xlUp).Row
            Me.Controls("ComboBox" & j).AddItem sh2.Cells(i, j)
            Me.Controls("ComboBox" & j).Style = fmStyleDropDownList[/COLOR]
        Next
    Next
End Sub

I deleted combobox5 and replaced it with textbox10. I made the necessary changes to the listindex entries and the initial priivate subs until I hit a wall with the last one that I highlighted in red. I am not sure how to modify it. Any help will be greatly appreciated.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,827
Office Version
  1. 2013
Platform
  1. Windows
Not sure about this since I'm not able to test it. Would like to recommend to try it on a copy of your workbook.

VBA Code:
Dim sh1 As Worksheet, sh2 As Worksheet
Private Sub CommandButton1_Click()
    Dim lr As Long
    If TextBox1.Value = "" Or Not IsDate(TextBox1) Then
        MsgBox "Enter a date", vbExclamation
        TextBox1.SetFocus
        Exit Sub
    End If
    '
    lr = sh1.Range("A" & Rows.Count).End(xlUp)(2).Row
    sh1.Cells(lr, "A").Value = TextBox1.Value   'DATE
    sh1.Cells(lr, "B").Value = TextBox2.Value   'DATE SUB
    sh1.Cells(lr, "C").Value = TextBox3.Value   'JOB#
    sh1.Cells(lr, "D").Value = ComboBox1.Value  'AGENCY
    sh1.Cells(lr, "E").Value = TextBox4.Value   'JOB NOTES
    sh1.Cells(lr, "F").Value = ComboBox2.Value  'ORDER
    sh1.Cells(lr, "G").Value = ComboBox3.Value  'COPY PAY ST
    sh1.Cells(lr, "H").Value = ComboBox4.Value  'EXPECTED BY
    sh1.Cells(lr, "U").Value = ComboBox10.Value 'CANCELLATION
    
    If TextBox5.Value <> "" Then
        sh1.Cells(lr, "I").Value = CDbl(TextBox5.Value)   'PAGES
    End If
    
'    If ComboBox5.Value <> "" Then
'        sh1.Cells(lr, "J").Value = CDbl(ComboBox5.Value)  'PAGE RATE
'    End If
    If TextBox10.Value <> "" Then
        sh1.Cells(lr, "J").Value = CDbl(TextBox10.Value)  'PAGE RATE
    End If
    
    
    If ComboBox6.Value <> "" Then
        sh1.Cells(lr, "M").Value = CDbl(ComboBox6.Value)  'VIDEO
    End If
    If ComboBox7.Value <> "" Then
        sh1.Cells(lr, "N").Value = CDbl(ComboBox7.Value)  'ROUGH
    End If
    If ComboBox8.Value <> "" Then
        sh1.Cells(lr, "O").Value = CDbl(ComboBox8.Value)  'LIVENOTE
    End If
    If TextBox6.Value <> "" Then
        sh1.Cells(lr, "S").Value = CDbl(TextBox6.Value)   'OT/DT
    End If
    If TextBox7.Value <> "" Then
        sh1.Cells(lr, "T").Value = CDbl(TextBox7.Value)   'PARKING
    End If
    If TextBox8.Value <> "" Then
        sh1.Cells(lr, "W").Value = CDbl(TextBox8.Value)   'OTHER FEES
    End If
    If ComboBox9.Value <> "" Then
        sh1.Cells(lr, "X").Value = CDbl(ComboBox9.Value)   'PER DIEM
    End If
    If TextBox9.Value <> "" Then
        sh1.Cells(lr, "AA").Value = CDbl(TextBox9.Value)  'SCOPING FEES
    End If
        
    ComboBox1.ListIndex = -1
    ComboBox2.ListIndex = -1
    ComboBox3.ListIndex = -1
    ComboBox4.ListIndex = -1
    
    'ComboBox5.ListIndex = -1
    TextBox10.Value = ""
    
    ComboBox6.ListIndex = -1
    ComboBox7.ListIndex = -1
    ComboBox8.ListIndex = -1
    ComboBox9.ListIndex = -1
    ComboBox10.ListIndex = -1
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
End Sub



Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'PAGES
    If Not (KeyAscii >= 48 And KeyAscii <= 57) Then KeyAscii = 0
End Sub
Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'OT/DT
    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0
End Sub
Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'PARKING
    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0
End Sub
Private Sub TextBox8_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'OTHER FEES
    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0
End Sub
Private Sub TextBox9_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'SCOPING FEES
    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0
End Sub

Private Sub TextBox10_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'PAGE RATE
    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0
End Sub


Private Sub UserForm_Activate()
    Set sh1 = Sheets("Receivables")
    Set sh2 = Sheets("Data")
    '
    For j = 1 To Columns("J").Column
        For i = 2 To sh2.Cells(Rows.Count, j).End(xlUp).Row
            If j = 5 Then Exit For      '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            Me.Controls("ComboBox" & j).AddItem sh2.Cells(i, j)
            Me.Controls("ComboBox" & j).Style = fmStyleDropDownList
        Next
    Next
End Sub
 
Solution

dosman

New Member
Joined
Jan 24, 2019
Messages
35
I will test it and thank you! I can provide you a copy of the original workboot with limited data if it does not work. I truly appreciate your response
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,827
Office Version
  1. 2013
Platform
  1. Windows
You are welcome. I'll keep watching so if it doesn't work, let me know.
 

dosman

New Member
Joined
Jan 24, 2019
Messages
35
The corrections you made to the last sub seems to have fixed it! Thank you!
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,827
Office Version
  1. 2013
Platform
  1. Windows
You're welcome & thanks for posting back.
 

Forum statistics

Threads
1,176,089
Messages
5,901,335
Members
434,886
Latest member
qazibelal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top