VBA form question

dosman

New Member
Joined
Jan 24, 2019
Messages
35
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.
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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
 
Upvote 0
I very much appreciate your help! I will look over the spreadsheet today and reply with comments.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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....
 
Upvote 0
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.


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
    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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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
Back
Top