Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Run through userforms to input code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Run through userforms to input code

    Hi

    I have 16 Userforms. Labeled as Userform1, Userform2.......Userform15, Userform16.

    I have prompt text boxes in each of these User forms.

    I need a for a loop that will allow me to run through all these forms allowing me to input code in each form.

    I was thinking

    For index AsInteger = 1To 16
    Userform index.ShowNext index

    But this doesnt work cuz I get error saying that Userform is not defined.

    How can I get around this?
    Thanks

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,828
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Raza

    I have been using UserForms for a long time and never needed more then one to do a job.

    Why would you need 16 UserForms?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,824
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Raza

    Hello Armghan Haider,

    This will cycle throught your UserForms. Copy this code in a VBA Module.

    Code:
    Sub ShowUserForms()
    
    
        Dim VBcomp As Object
        
            For Each VBcomp In ThisWorkbook.VBProject.VBComponents
                If VBcomp.Type = 3 Then
                    UserForms.Add VBcomp.Name
                    UserForms(0).Show
                End If
            Next VBcomp
        
    End Sub
    Sincerely,
    Leith Ross

  4. #4
    New Member
    Join Date
    Jun 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Raza

    Quote Originally Posted by Leith Ross View Post
    Hello Armghan Haider,

    This will cycle throught your UserForms. Copy this code in a VBA Module.

    Code:
    Sub ShowUserForms()
    
    
        Dim VBcomp As Object
        
            For Each VBcomp In ThisWorkbook.VBProject.VBComponents
                If VBcomp.Type = 3 Then
                    UserForms.Add VBcomp.Name
                    UserForms(0).Show
                End If
            Next VBcomp
        
    End Sub

    Thanks this works!!!

  5. #5
    New Member
    Join Date
    Jun 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Raza

    Yeah, I am not being very efficient with my code. I should have a made a for loop to loop through the code inside one userform but I am new and i dont know how to yet.

    But basically what I am doing is each userform asks the user for an input in 3 text boxes and then the content of the 3 text boxes is pasted in C3,D3,E3. Userform 2 would out put in C4,D4,E3.

  6. #6
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,824
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Raza

    Hello Armghan Haider,

    Are you saying you do not have the UserForm code to copy to the text boxes to the cells?
    Sincerely,
    Leith Ross

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,828
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Raza

    So what your want to do is put data into row 3 to 19
    On each row you want to enter data into column C D and E
    Is this true?

    Now do you always want to stat in row 3
    Or do you want to start in the first empty cell in column C

    So if you already have data in C3 and C4 and C5 we will start in C6

    Would this do what you want.

    We could do all this with just one Userform having 48 Text boxes

    Would this work?

    If so put 48 textboxes in your userForm

    Have them keep the default names

    So they would be named
    Textbox1
    Textbox2

    And so on

    Or maybe you might want to tell me what you plan to put in these Textboxes and I would know of a way to do this without 48 textboxes
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    New Member
    Join Date
    Jun 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Raza

    "Public POV As IntegerPublic lngIndex As Long
    Public objComp As VBComponent
    Public lngItem As Long
    Public objCnt As Object


    Public Sub Ready_Up_PO()
    '
    ' Ready_Up_PO Macro
    ' Readys up WO
    '
    ' Keyboard Shortcut: Ctrl+b
    POV = 1

    Call ShowUserForms
    If POV = 0 Then
    Exit Sub

    End If



    Range("B19").Select
    ActiveCell.FormulaR1C1 = "8774"
    Range("B20").Select
    ActiveCell.FormulaR1C1 = "armghan.haider@xxxx.com"
    Range("A22").Select

    With Range("A22")
    .Value = Date
    .NumberFormat = "mm/dd/yy"
    End With


    Range("B22").Select
    ActiveCell.FormulaR1C1 = "Armghan Haider"


    Range("D11").Select
    Selection.ClearContents 'clear cell with PO#'

    With Range("D11")
    .Value = Date 'inputs date'


    .NumberFormat = "mm/dd/yy"
    End With


    Dim cell As Range
    For Each cell In Selection
    cell.Value = cell.Value + 14 ' adds 14 days
    Next cell




    Range("D4").Select
    Range("B7").Select
    ActiveWorkbook.SaveAs filename:= _
    "Y:\Performance\Performance_Engineering\Armghan Haider\PO#" & Range("D4").Value & "-" & Range("B7").Value & ".xlsm", FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


    End Sub


    Sub ShowUserForms()


    Dim VBcomp As Object

    For Each VBcomp In ThisWorkbook.VBProject.VBComponents
    If VBcomp.Type = 3 Then
    UserForms.Add VBcomp.Name
    UserForms(0).Show

    End If
    Next VBcomp

    End Sub


    And then I have 15 User forms with this sort of code.
    USER FORM 0
    Private Sub TextBox1_Change()
    Sheets(1).Range("D4").Value = TextBox1.Value
    End Sub


    Private Sub ToggleButton1_Click()
    Unload Me
    End Sub


    Private Sub ToggleButton2_Click()
    Sheets(1).Range("D4").Value = ""
    Unload Me
    POV = 0
    End Sub


    Private Sub UserForm_Click()


    End Sub


    USER-FORM 1
    Private Sub ComboBox1_Change()
    Sheets(1).Range("A28").Value = ComboBox1.Value


    End Sub






    Private Sub CommandButton1_Click()
    Unload Me
    End Sub


    Private Sub Frame1_Click()


    End Sub


    Private Sub TextBox1_Change()
    Sheets(1).Range("B28").Value = TextBox1.Value
    End Sub


    Private Sub UserForm_Initialize()
    Me.ComboBox1.AddItem "1"
    Me.ComboBox1.AddItem "2"
    Me.ComboBox1.AddItem "3"
    Me.ComboBox1.AddItem "4"
    Me.ComboBox1.AddItem "5"
    Me.ComboBox1.AddItem "6"
    Me.ComboBox1.AddItem "7"
    Me.ComboBox1.AddItem "8"
    Me.ComboBox1.AddItem "9"
    Me.ComboBox1.AddItem "10"
    Me.ComboBox1.AddItem "11"
    Me.ComboBox1.AddItem "12"
    Me.ComboBox1.AddItem "13"
    Me.ComboBox1.AddItem "14"
    Me.ComboBox1.AddItem "15"
    Me.ComboBox1.AddItem "16"
    Me.ComboBox1.AddItem "17"
    Me.ComboBox1.AddItem "18"
    Me.ComboBox1.AddItem "19"
    Me.ComboBox1.AddItem "20"
    End Sub


    Private Sub TextBox2_Change()
    Sheets(1).Range("D28").Value = TextBox2.Value
    End Sub


    Private Sub TextBox3_Change()
    Sheets(1).Range("C28").Value = TextBox3.Value
    End Sub


    USER FORM 2
    Private Sub ComboBox1_Change()
    Sheets(1).Range("A29").Value = ComboBox1.Value


    End Sub


    Private Sub Frame1_Click()


    End Sub


    Private Sub CommandButton1_Click()
    Unload Me
    End Sub


    Private Sub TextBox1_Change()
    Sheets(1).Range("B29").Value = TextBox1.Value
    End Sub


    Private Sub UserForm_Initialize()
    Me.ComboBox1.AddItem "1"
    Me.ComboBox1.AddItem "2"
    Me.ComboBox1.AddItem "3"
    Me.ComboBox1.AddItem "4"
    Me.ComboBox1.AddItem "5"
    Me.ComboBox1.AddItem "6"
    Me.ComboBox1.AddItem "7"
    Me.ComboBox1.AddItem "8"
    Me.ComboBox1.AddItem "9"
    Me.ComboBox1.AddItem "10"
    Me.ComboBox1.AddItem "11"
    Me.ComboBox1.AddItem "12"
    Me.ComboBox1.AddItem "13"
    Me.ComboBox1.AddItem "14"
    Me.ComboBox1.AddItem "15"
    Me.ComboBox1.AddItem "16"
    Me.ComboBox1.AddItem "17"
    Me.ComboBox1.AddItem "18"
    Me.ComboBox1.AddItem "19"
    Me.ComboBox1.AddItem "20"
    End Sub


    Private Sub TextBox2_Change()
    Sheets(1).Range("D29").Value = TextBox2.Value
    End Sub


    Private Sub TextBox3_Change()
    Sheets(1).Range("C29").Value = TextBox3.Value
    End Sub


    Private Sub ToggleButton1_Click()
    Unload Me
    End Sub

    AND SO ON

    The plan is when you start the macro:

    A prompt shows up that asks you to enter the PO.
    Once you enter it and submit, (Userform 0)
    Another prompt shows up that asks for the number of items (1 to 15)
    Once you enter (e.g 4) and submit.
    Now a for loop runs where Userform 1 to 4 will show in sequence:
    Each of these Userform will ask for item description. This description will be inputed into cells going down as a list.
    C28 D28 E28
    C29 D29 E29
    C30 D30 E30
    C31 D31 E31

    Once the 4 userforms filled, the user will click close form and that will exit them out of the 'Userform Loop'

    and the rest of the code which is just entering stuff will run and done.



    Sorry if this seems long and tiresome just read the bolded stuff for general understanding . All advice is appreciated. Thanks

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,828
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Raza

    Sounds like you want to do things your way with 16 Userforms.
    I have no advise on doing things this way.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,824
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Raza

    Hello Armghan Haider,

    It would be easier for me to help you if I had a copy of the workbook. Can you post the workbook to a public file sharing site like DropBox or MediaFire? If that isn't an option perhaps you could email me a copy.
    Sincerely,
    Leith Ross

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
  •