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?

 

[Siddharth Rout]

>Is it possible to open the same (userform1) with two different command buttons and get different results.

Hope I have understood your query. If not, feel free to explain it a bit more.

Here is an example. Let's say you have 2 userfom. The first one is userform1 and the 2nd one is Cust_Line_Order_Form. And both have command buttons.

In a module

Sub Sample()
    Dim frm As New UserForm1
  
    frm.Show
End Sub

In Userform 1

Private Sub CommandButton1_Click()
    Dim CustFrm As New Cust_Line_Order_Form
    CustFrm.Show
End Sub

In Cust_Line_Order_Form

Private Sub CommandButton1_Click()
    Dim frm As New UserForm1
  
    frm.Show
End Sub

Run Sub Sample(). That will launch an instance of UserForm1. Click on the CommandButton of UserForm1. It will launch an instance of the Cust_Line_Order_Form. Click on the CommandButton of Cust_Line_Order_Form. That will launch an instance of UserForm1.

 

[rharri1972]

>Is it possible to open the same (userform1) with two different command buttons and get different results.

Hope I have understood your query. If not, feel free to explain it a bit more.

Here is an example. Let's say you have 2 userfom. The first one is userform1 and the 2nd one is Cust_Line_Order_Form. And both have command buttons.

In a module

Sub Sample()
    Dim frm As New UserForm1
 
    frm.Show
End Sub

In Userform 1

Private Sub CommandButton1_Click()
    Dim CustFrm As New Cust_Line_Order_Form
    CustFrm.Show
End Sub

In Cust_Line_Order_Form

Private Sub CommandButton1_Click()
    Dim frm As New UserForm1
 
    frm.Show
End Sub

Run Sub Sample(). That will launch an instance of UserForm1. Click on the CommandButton of UserForm1. It will launch an instance of the Cust_Line_Order_Form. Click on the CommandButton of Cust_Line_Order_Form. That will launch an instance of UserForm1.

View attachment 72066

>Is it possible to open the same (userform1) with two different command buttons and get different results.

Hope I have understood your query. If not, feel free to explain it a bit more.

Here is an example. Let's say you have 2 userfom. The first one is userform1 and the 2nd one is Cust_Line_Order_Form. And both have command buttons.

In a module

Sub Sample()
    Dim frm As New UserForm1
 
    frm.Show
End Sub

In Userform 1

Private Sub CommandButton1_Click()
    Dim CustFrm As New Cust_Line_Order_Form
    CustFrm.Show
End Sub

In Cust_Line_Order_Form

Private Sub CommandButton1_Click()
    Dim frm As New UserForm1
 
    frm.Show
End Sub

Run Sub Sample(). That will launch an instance of UserForm1. Click on the CommandButton of UserForm1. It will launch an instance of the Cust_Line_Order_Form. Click on the CommandButton of Cust_Line_Order_Form. That will launch an instance of UserForm1.

>Is it possible to open the same (userform1) with two different command buttons and get different results.

Hope I have understood your query. If not, feel free to explain it a bit more.

Here is an example. Let's say you have 2 userfom. The first one is userform1 and the 2nd one is Cust_Line_Order_Form. And both have command buttons.

In a module

Sub Sample()
    Dim frm As New UserForm1
 
    frm.Show
End Sub

In Userform 1

Private Sub CommandButton1_Click()
    Dim CustFrm As New Cust_Line_Order_Form
    CustFrm.Show
End Sub

In Cust_Line_Order_Form

Private Sub CommandButton1_Click()
    Dim frm As New UserForm1
 
    frm.Show
End Sub

Run Sub Sample(). That will launch an instance of UserForm1. Click on the CommandButton of UserForm1. It will launch an instance of the Cust_Line_Order_Form. Click on the CommandButton of Cust_Line_Order_Form. That will launch an instance of UserForm1.

View attachment 72066

Sid... thank you for such a quick reply. I do have a hard time sometimes figuring out how to explain what I am looking for....If you don't mind I will attempt a flow chart.... hoping this helps....

Customer Order Form (user Form)-----> INSERT button----> Opens Customer Line Order Form (UserForm) with blank values ----> command button ----> opens Parts Select Table (User Form) -----> Here i will highlight the part I want in a parts list and click command button (GO). The subroutine in this button looks for the listbox list index value and activates that cell in the worksheet.

I will then click command button OK on the same Parts Select Table. IF...IF i use as part of this subroutine.... Cust_Line_Order_Form_Activate() for textbox 3 to = the value of the active cell... it works fine UNTIL.... the next time I go to open it again FROM the customer order form userform....(when its supposed to open with blank values because it is looking for the values from the worksheet on activate so it crashes.

 

[rharri1972]

Sid... thank you for such a quick reply. I do have a hard time sometimes figuring out how to explain what I am looking for....If you don't mind I will attempt a flow chart.... hoping this helps....

Customer Order Form (user Form)-----> INSERT button----> Opens Customer Line Order Form (UserForm) with blank values ----> command button ----> opens Parts Select Table (User Form) -----> Here i will highlight the part I want in a parts list and click command button (GO). The subroutine in this button looks for the listbox list index value and activates that cell in the worksheet.

I will then click command button OK on the same Parts Select Table. IF...IF i use as part of this subroutine.... Cust_Line_Order_Form_Activate() for textbox 3 to = the value of the active cell... it works fine UNTIL.... the next time I go to open it again FROM the customer order form userform....(when its supposed to open with blank values because it is looking for the values from the worksheet on activate so it crashes.

adding to this post.... I almost feel like or want to say... if userform (Cust_Line_Order_Form) is opening from the INSERT button from Userform (Customer Order Form) then open with blank values... else....if you are opening from userform(Part Select Table) command button (OK)... then update textbox3 with the active cell of worksheet "Inventory".

If that is right I wouldn't even know how code that.

 

[Siddharth Rout]

I would like to help you but I am embarrassed to say that I lost you on the 2nd part...

The first part is not a problem. The below can be achieved

Customer Order Form (user Form)-----> INSERT button----> Opens Customer Line Order Form (UserForm) with blank values ----> command button ----> opens Parts Select Table (User Form) -----> Here i will highlight the part I want in a parts list and click command button (GO). The subroutine in this button looks for the listbox list index value and activates that cell in the worksheet.

I am not able to understand what you are trying to say in the 2nd part.

I will then click command button OK on the same Parts Select Table. IF...IF i use as part of this subroutine.... Cust_Line_Order_Form_Activate() for textbox 3 to = the value of the active cell... it works fine UNTIL.... the next time I go to open it again FROM the customer order form userform....(when its supposed to open with blank values because it is looking for the values from the worksheet on activate so it crashes.

 

[rharri1972]

I would like to help you but I am embarrassed to say that I lost you on the 2nd part...

The first part is not a problem. The below can be achieved



I am not able to understand what you are trying to say in the 2nd part.

I'm sorry for the confusion and I completely appreciate and am grateful for your help!!! So the issue...I THINK....is that inside the userform (Cust_Line_Order_Form) i have on userform_Activate()... for textbox3.value to equal the value of the active cell from worksheet "Inventory".

So when I orginially want to open the Cust_Line userform to enter a new product for a new order... and I hit the INSERT button from the Customer Order Form... because of the before mentioned code in the Activate() event.... it crashes because it is looking for the values that haven't even been selected yet.

Originally I want to open the Cust_Line userform with blank values... from Parts_Select_Table form I want to select my part.... hit OK button and be able to re-open the Cust_Line form again but this time with the values. The next building step is... I want to hit a button at that time that will send those values to a worksheet for records keeping... also clearing the textbox values to select another line item of parts if there are more than one line... I THINK i can do that part if i can figure out this first issue.

 

[Siddharth Rout]

Possible to see your excel file? If yes, can you upload it to say dropbox or onedrive and share the link here? If yes, then ensure you remove any confidential information before you upload it.

 

[rharri1972]

Possible to see your excel file? If yes, can you upload it to say dropbox or onedrive and share the link here? If yes, then ensure you remove any confidential information before you upload it.

I wouldn't mind but dont know how? I know I should know how...sorry

 

[rharri1972]

Possible to see your excel file? If yes, can you upload it to say dropbox or onedrive and share the link here? If yes, then ensure you remove any confidential information before you upload it.

Is there such a thing as
If userform opened with this button then open blank...else if opened with this button update textbox values?

 

[Siddharth Rout]

Is there such a thing as
If userform opened with this button then open blank...else if opened with this button update textbox values?

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