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
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.
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 SubI 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.
