Open the same userform with two different button with two different result

[rharri1972]

Hello!

I have also posted this for help to OZgrid.com with 43 reviews and 0 answers.



Is it possible to open the same (userform1) with two different command buttons and get different results. The first button (INSERT) on (userform3)is to open the userform Cust_Line_Order_Form (with textboxes) in a blank value state.

a button on this form then opens Userform Product Table.



a command button (OK) on (userform Product Table) is to open the userform1 with values filled into the textboxes. A value selected from a listbox on userform Product Table, activates the value in a cell on a worksheet "INVENTORY".



That value plus additional cell values are to show in USERFORM1 textboxes.



So far it crashes.



for button 1 (INSERT) on Userform3 I have:

Cust_Line_Order_Form.show  '<-- opens form in blank value state...just a userform with blank textboxes

commandbutton 2 on userform (Cust_Line_Order_Form)... opens userform (Product table) with a listbox of thousands of product... I hightlight product needed and hit "OK" command button that has this code:

unload me
Cust_Line_Order_Form.show

In the userform_Activate() for userform Cust_Line_Order_Form

I have code:

Optionbutton1.visible=True '<--- has nothing to do with values

me.textbox3.value = sheets("Inventory").activecell.value

Once I can get this to work I plan to use the offset function to complete the other textboxes. However, It crashes.

Obviously because I am trying to open the same userform in two different states but I can't figure out if there should be an (IF) statement or any other kind of "work around".

I am basically trying to process a customers order. The Cust_Line_Order_Form will contain each line items values. Once the line items are entered, this form will go back to blank values waiting for the next line's values.



Any help?

 

[rharri1972]

Yes. Declare a public variable in the 2nd userform (Lets call it Userform2). Something like this

Public OpenOption As Long

Private Sub UserForm_Activate()
    If OpenOption = 1 Then
        TextBox3.Text = ""
    ElseIf OpenOption = 2 Then
        TextBox3.Text = "WhatEver"
    End If
End Sub

And then launch Userform2 like this from Userform1

Private Sub CommandButton1_Click()
    Dim frm As New UserForm2
    frm.OpenOption = 1
    frm.Show
End Sub

Private Sub CommandButton2_Click()
    Dim frm As New UserForm2
    frm.OpenOption = 2
    frm.Show
End Sub

gave me a method or data memeber not found compile error.

Private Sub CommandButton2_Click()
    Dim frm As New Cust_Line_Order_Form
    frm.OpenOption = 1
    frm.Show
End Sub

It highlighted OpenOption = 1

I did change Userform2 to what the actual userform is called... was that a mistake?

I also notice the the Public Variable is NOT in the same box or event as the rest of the code. Is the Public Variable supposed to be in a new Module? I'm still new which is probably why I may be aggravating you. My apologies.

 

[Siddharth Rout]

gave me a method or data memeber not found compile error.

Can you share the exact code? The code that I posted above is tried and tested.

 

[rharri1972]

Yes. Declare a public variable in the 2nd userform (Lets call it Userform2). Something like this

Public OpenOption As Long

Private Sub UserForm_Activate()
    If OpenOption = 1 Then
        TextBox3.Text = ""
    ElseIf OpenOption = 2 Then
        TextBox3.Text = "WhatEver"
    End If
End Sub

And then launch Userform2 like this from Userform1

Private Sub CommandButton1_Click()
    Dim frm As New UserForm2
    frm.OpenOption = 1
    frm.Show
End Sub

Private Sub CommandButton2_Click()
    Dim frm As New UserForm2
    frm.OpenOption = 2
    frm.Show
End Sub

Sid!!!! It works... I googled what to do with a public variable....placed it under the delclaration under GENERAL. ran back through it and it works!!! You have been amazing through this process...thank you so much for your help, patience and promptness!!!!

 

[rharri1972]

Can you share the exact code? The code that I posted above is tried and tested.

Sid...I mis-spoke... I am getting an object doesn't support this property or method runtime error "438". Here is the code.... and this is on the Cust_Line_Order_Form

Private Sub UserForm_Activate()
    If OpenOption = 1 Then
       Me.TextBox3.Value = ""
    Else
        Me.TextBox3.Value = Sheets("PARTS LIST").ActiveCell.Text
        End If
End Sub

 

[Siddharth Rout]

Sheets("PARTS LIST").ActiveCell.Text

should either be

Selection.Value

or

ActiveCell.Value

 

[rharri1972]

Sid...I mis-spoke... I am getting an object doesn't support this property or method runtime error "438". Here is the code.... and this is on the Cust_Line_Order_Form

Private Sub UserForm_Activate()
    If OpenOption = 1 Then
       Me.TextBox3.Value = ""
    Else
        Me.TextBox3.Value = Sheets("PARTS LIST").ActiveCell.Text
        End If
End Sub

Yes. Declare a public variable in the 2nd userform (Lets call it Userform2). Something like this

Public OpenOption As Long

Private Sub UserForm_Activate()
    If OpenOption = 1 Then
        TextBox3.Text = ""
    ElseIf OpenOption = 2 Then
        TextBox3.Text = "WhatEver"
    End If
End Sub

And then launch Userform2 like this from Userform1

Private Sub CommandButton1_Click()
    Dim frm As New UserForm2
    frm.OpenOption = 1
    frm.Show
End Sub

Private Sub CommandButton2_Click()
    Dim frm As New UserForm2
    frm.OpenOption = 2
    frm.Show
End Sub

Sid...can we revisit this please...As good as you are I have to believe I have mixed this up somewhere.... It is now calling the userform blank with both buttons so I am going to give you exact names.... each form is labeled in quotations...

"Customer Order Form" ----> "INSERT" button. This opens the first instance of :

"Cust_Line_Order_Form" (Should have "" values in all textboxes) 1,3,4,&5 ----> command button 2 (No Name)

opens "Part Select Table" and once i find the part i need -----> "Select" button

this opens the second instance of "Cust_Line_Order_Form" where the data should show in textbox 3 from my selection.

I added the public variable in "Cust_Line_Order_Form" under General/Declarations and

Private Sub UserForm_Activate()
    If OpenOption = 1 Then
        TextBox3.Text = ""
    ElseIf OpenOption = 2 Then
        TextBox3.Text = "WhatEver"
    End If
End Sub

is added to the userform_Activate() for "Cust_Line_Order_Form"

but now I am lost with where to place :

"And then launch Userform2 like this from Userform1"

Private Sub CommandButton1_Click()
    Dim frm As New UserForm2
    frm.OpenOption = 1
    frm.Show
End Sub

Private Sub CommandButton2_Click()
    Dim frm As New UserForm2
    frm.OpenOption = 2
    frm.Show
End Sub

I am thinking in this case you are calling userform 2 "Cust_List_Order_Form" and userform 1 the "Customer Order Form" .
However, what is confusing me is the fact that on "Customer Order Form" the "INSERT" button is command button 2 which I realize you were just calling out a number, no big deal....

but the other button that opens "Cust_Line_Order_Form" the second time is on userform "Part Select Table".
This did work the other day when you sent it to me but it does not... and learning all of this and with all of this code my brain feels like mush.
Can you help straighten me out on this?

 

[rharri1972]

Sid...can we revisit this please...As good as you are I have to believe I have mixed this up somewhere.... It is now calling the userform blank with both buttons so I am going to give you exact names.... each form is labeled in quotations...

"Customer Order Form" ----> "INSERT" button. This opens the first instance of :

"Cust_Line_Order_Form" (Should have "" values in all textboxes) 1,3,4,&5 ----> command button 2 (No Name)

opens "Part Select Table" and once i find the part i need -----> "Select" button

this opens the second instance of "Cust_Line_Order_Form" where the data should show in textbox 3 from my selection.

I added the public variable in "Cust_Line_Order_Form" under General/Declarations and

Private Sub UserForm_Activate()
    If OpenOption = 1 Then
        TextBox3.Text = ""
    ElseIf OpenOption = 2 Then
        TextBox3.Text = "WhatEver"
    End If
End Sub

is added to the userform_Activate() for "Cust_Line_Order_Form"

but now I am lost with where to place :

"And then launch Userform2 like this from Userform1"

Private Sub CommandButton1_Click()
    Dim frm As New UserForm2
    frm.OpenOption = 1
    frm.Show
End Sub

Private Sub CommandButton2_Click()
    Dim frm As New UserForm2
    frm.OpenOption = 2
    frm.Show
End Sub

I am thinking in this case you are calling userform 2 "Cust_List_Order_Form" and userform 1 the "Customer Order Form" .
However, what is confusing me is the fact that on "Customer Order Form" the "INSERT" button is command button 2 which I realize you were just calling out a number, no big deal....

but the other button that opens "Cust_Line_Order_Form" the second time is on userform "Part Select Table".
This did work the other day when you sent it to me but it does not... and learning all of this and with all of this code my brain feels like mush.
Can you help straighten me out on this?

Sid....I went back to your original and broke it down....made my head hurt but i think i got it. I put option 1 in the button for the Customer Order Form and Option 2 in the button on the Part Select Table Form. Sorry Im so scattered but I am trying to learn. Boy...it's got its challenges!! So I think im good on this.