VBA form question

[dosman]

I have created a 23 column spreadsheet with data validation cells using drop down lists and a few columns that do the standard date formatting (4/18/19 displays as Thursday, April 18, 2019)

Converting it to a table is not desirable and I would love to have a user form that "pulls" the validations from the existing cells in 2 sheets. Once the form creates the next row in the sheet I need to be able to go back and adjust values in the drop down lists without having to use the form which I only need to create a new row of data.

I am not a VB coder (although I am learning) but this is above my current pay grade. I do not know if the form would require me to clear the validation from a cell to allow it to do its magic or if the form can merely mirror the validations and formatting that already exist. I need the drop downs to still be functional once the form creates the row.

The form would only require a button on the main sheet labeled "New Job" with an "Add" button at the bottom. I can create the form using the VBA editor but am not sure of the code to insert.

I am more than willing to share the spreadsheet for analysis if anyone can help.

Best regards to an awesome community.

 

[DanteAmor]

I help you with the creation of the userform, to add records to your sheet.

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[dosman]

Thank you for your offer to help! I will create a share and post the link very soon!

 

[dosman]

I have color coded the column labels to hopefully help quick analysis


I would like all columns to be included in the form except those in blue. They ae calculations based on the input values.


The black labels are date validations as described. Gray columns are pure text input. The orange columns are where the drop down validations exist.


The data sheet is where I am storing drop down choices.


Again,


Thank You!

https://www.dropbox.com/s/v1nmb367mt6m7su/MrExcel.xlsx?dl=0

 

[DanteAmor]

I attached an advance.
There are several or many things to do, but begin to try the following.
You can modify the design of the userform and upload the file with your comments.

https://www.dropbox.com/s/712upu4q8w80nql/MrExcel userform.xlsm?dl=0

 

[dosman]

I very much appreciate your help! I will look over the spreadsheet today and reply with comments.

 

[dosman]

I have reviewed the advance and this definitely a good start. I see no reason to modify the form at this point in development. I am curious about a few things in the copy you shared.

I originally set up data validation for 10,000 rows knowing it would suffice long after the spreadsheet was needed. I notice the validation was stripped from all cells below your test row. Being able to return to the sheet after a row has been added and change values is critical with the validation conditions met. If I need to change the Expected By value in column H from 3 to 2 I need to be able to do so from the cell with the drop down choices in tact. If this can be done by calling the form for row editing that would be sufficient. If the form can only add new rows the validations I formatted in all columns must still be usable.

I also noticed i could not use numbers unless my Num Lock key was turned off.

Other than that I see this being a great start to what I was hoping to achieve.

Your help is, again, greatly appreciated.

 

[DanteAmor]

Validations of the cells can continue, I just removed them for my tests, but you can place them without problem.


The edition from the userform is a fairly broad topic, but if you have considered making the changes from the sheet, then you can use the validations without problem.

 

[dosman]

That is definitely good news. I liked your initial layout of the form. The groupings were were nice. From your earlier post I understood I can always make changes the formatting without affecting the code. If validation can still work without the form then I'd say you are providing exactly what I was hoping for. Thanks you again for your work on this! The advance looks great....

 

[DanteAmor]

Use the following code with the following improvements:
- In the combos, you can only select an item from the list
- Validate only numbers in textbox
- Load in the sheet the data contained in the textbox or combo as text, put it as a number.

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
    If TextBox2.Value = "" Or Not IsDate(TextBox2) Then
        MsgBox "Enter a date", vbExclamation
        TextBox2.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
[COLOR=#0000ff]    If TextBox5.Value <> "" Then[/COLOR]
[COLOR=#0000ff]        sh1.Cells(lr, "I").Value = CDbl(TextBox5.Value)   'PAGES[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]    If ComboBox5.Value <> "" Then[/COLOR]
[COLOR=#0000ff]        sh1.Cells(lr, "J").Value = CDbl(ComboBox5.Value)  'PAGE RATE[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]    If ComboBox6.Value <> "" Then[/COLOR]
[COLOR=#0000ff]        sh1.Cells(lr, "M").Value = CDbl(ComboBox6.Value)  'VIDEO[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]    If ComboBox7.Value <> "" Then[/COLOR]
[COLOR=#0000ff]        sh1.Cells(lr, "N").Value = CDbl(ComboBox7.Value)  'ROUGH[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]    If ComboBox8.Value <> "" Then[/COLOR]
[COLOR=#0000ff]        sh1.Cells(lr, "O").Value = CDbl(ComboBox8.Value)  'LIVENOTE[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]    If TextBox6.Value <> "" Then[/COLOR]
[COLOR=#0000ff]        sh1.Cells(lr, "S").Value = CDbl(TextBox6.Value)   'OT/DT[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]    If TextBox7.Value <> "" Then[/COLOR]
[COLOR=#0000ff]        sh1.Cells(lr, "T").Value = CDbl(TextBox7.Value)   'PARKING[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]    If TextBox8.Value <> "" Then[/COLOR]
[COLOR=#0000ff]        sh1.Cells(lr, "U").Value = CDbl(TextBox8.Value)   'OTHER FEES[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
End Sub


[COLOR=#0000ff]Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)[/COLOR]
[COLOR=#0000ff]'JOB#[/COLOR]
[COLOR=#0000ff]    If Not (KeyAscii >= 48 And KeyAscii <= 57) Then KeyAscii = 0[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]
[COLOR=#0000ff]Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)[/COLOR]
[COLOR=#0000ff]'PAGES[/COLOR]
[COLOR=#0000ff]    If Not (KeyAscii >= 48 And KeyAscii <= 57) Then KeyAscii = 0[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]
[COLOR=#0000ff]Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)[/COLOR]
[COLOR=#0000ff]'OT/DT[/COLOR]
[COLOR=#0000ff]    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]
[COLOR=#0000ff]Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)[/COLOR]
[COLOR=#0000ff]'PARKING[/COLOR]
[COLOR=#0000ff]    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]
[COLOR=#0000ff]Private Sub TextBox8_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)[/COLOR]
[COLOR=#0000ff]'OTHER FEES[/COLOR]
[COLOR=#0000ff]    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 46 Then KeyAscii = 0[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]


Private Sub UserForm_Activate()
    Set sh1 = Sheets("Receivables")
    Set sh2 = Sheets("Data")
    '
    For j = 1 To Columns("H").Column
        For i = 2 To sh2.Cells(Rows.Count, j).End(xlUp).Row
            Me.Controls("ComboBox" & j).AddItem sh2.Cells(i, j)
[COLOR=#0000ff]            Me.Controls("ComboBox" & j).Style = fmStyleDropDownList[/COLOR]
        Next
    Next
End Sub