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

rharri1972

Board Regular
Joined
Nov 12, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
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:

VBA Code:
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:

VBA Code:
unload me
Cust_Line_Order_Form.show


In the userform_Activate() for userform Cust_Line_Order_Form

I have code:


VBA 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

Board Regular
Joined
Nov 12, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
Yes. Declare a public variable in the 2nd userform (Lets call it Userform2). Something like this

VBA Code:
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

VBA Code:
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.

Rich (BB code):
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.
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

rharri1972

Board Regular
Joined
Nov 12, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
Yes. Declare a public variable in the 2nd userform (Lets call it Userform2). Something like this

VBA Code:
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

VBA Code:
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

Board Regular
Joined
Nov 12, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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

Active Member
Joined
Jan 7, 2022
Messages
359
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
VBA Code:
Sheets("PARTS LIST").ActiveCell.Text

should either be

VBA Code:
Selection.Value

or

VBA Code:
ActiveCell.Value
 

rharri1972

Board Regular
Joined
Nov 12, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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

VBA Code:
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

VBA Code:
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

VBA Code:
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"

VBA Code:
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

Board Regular
Joined
Nov 12, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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"

VBA Code:
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.
 

Forum statistics

Threads
1,181,058
Messages
5,927,882
Members
436,575
Latest member
Tiger750

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top