VBA form question

dosman

New Member
Joined
Jan 24, 2019
Messages
25
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,736
Office Version
2007
Platform
Windows
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

New Member
Joined
Jan 24, 2019
Messages
25
Thank you for your offer to help! I will create a share and post the link very soon!
 

dosman

New Member
Joined
Jan 24, 2019
Messages
25
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
 

dosman

New Member
Joined
Jan 24, 2019
Messages
25
I very much appreciate your help! I will look over the spreadsheet today and reply with comments.
 

dosman

New Member
Joined
Jan 24, 2019
Messages
25
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,736
Office Version
2007
Platform
Windows
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

New Member
Joined
Jan 24, 2019
Messages
25
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,736
Office Version
2007
Platform
Windows
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
 

Forum statistics

Threads
1,081,676
Messages
5,360,441
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top