Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34

Thread: VBA form question

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA form question

    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 by dosman; Apr 18th, 2019 at 08:17 PM.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,225
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: VBA form question

    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.
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA form question

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

  4. #4
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA form question

    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/v1nmb367mt...xcel.xlsx?dl=0

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,225
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: VBA form question

    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/712upu4q8w...form.xlsm?dl=0
    Regards Dante Amor

  6. #6
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA form question

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

  7. #7
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA form question

    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.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,225
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: VBA form question

    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.
    Regards Dante Amor

  9. #9
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA form question

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

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,225
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: VBA form question

    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
        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 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, "U").Value = CDbl(TextBox8.Value)   'OTHER FEES
        End If
    End Sub
    
    
    Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    'JOB#
        If Not (KeyAscii >= 48 And KeyAscii <= 57) Then KeyAscii = 0
    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 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)
                Me.Controls("ComboBox" & j).Style = fmStyleDropDownList
            Next
        Next
    End Sub
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •