Run through userforms to input code

[Armghan Haider]

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 As Integer = 1 To 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

 

[My Aswer Is This]

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?

 

[Leith Ross]

Re: Raza

Hello Armghan Haider,

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

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

 

[Armghan Haider]

Re: Raza

Hello Armghan Haider,

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

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

 

[Armghan Haider]

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.

 

[Leith Ross]

Re: Raza

Hello Armghan Haider,

Are you saying you do not have the UserForm code to copy to the text boxes to the cells?

 

[My Aswer Is This]

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

 

[Armghan Haider]

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

 

[My Aswer Is This]

Re: Raza

Sounds like you want to do things your way with 16 Userforms.
I have no advise on doing things this way.

 

[Leith Ross]

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.