Create Command Buttons based on Worksheet Names

Hi

What would the buttons do?

rylo

rylo,
The buttons when populated and then clicked would call a sub routine

Private Sub CommandButton1_Click()
 Call GetCmdCaption
End Sub

The sub routne would then take the command button caption store it in a string and use that to populate other forms. Example

Sub GetCmdCaption()
  CmdBtn = ActiveControl.Object.Caption
 'frmManufacturer.Caption = CmdBtn & " Main Menu"
 
 Select Case CmdBtn
                
       Case "FPX"
            Equipment = " "
            Me.Hide
            frmParts.Show
        Case "ART"
            Equipment = " "
            Me.Hide
            frmParts.Show
        Case "ICK"
            Equipment = " "
            Me.Hide
            frmParts.Show
    End Select
End Sub

CmdBtn and Equipment are delcared in Module1 as a strings. On the original file that I posted I left out all the sequential forms. My thought was that if someone was able to show me the logic on how to create the buttons I would be able to integrate it into the full workbook. If it proves to be usefull for everyone to see the full thing I will gladly post the full workbook. Thanks again for your help.

Bob

Hi

Here's something pretty generic that should get you going. I've just put in some simplistic positional incrementing numbers so they don't all jumble on top of each other.

rylo

Private Sub UserForm_Initialize()
  Dim i As Long, MyCmd As Control, Topper As Long, Lefter As Long
  Lefter = 1
  Topper = 1
  
  For i = 1 To Sheets.Count
    Set MyCmd = UserForm1.Controls.Add(bstrprogID:="Forms.CommandButton.1", Name:=Sheets(i).Name, Visible:=True)
    MyCmd.Top = Topper
    MyCmd.Left = Lefter
    MyCmd.Caption = Sheets(i).Name
    Lefter = Lefter + 80
    If Lefter > 440 Then
      Topper = Topper + 30
      Lefter = 1
    End If
  Next i
End Sub

Hi

Here's a bit more that may help you.

1) Create a new class module called Class1 (this is the default) and enter the code

Public WithEvents ButtonGroup As msforms.CommandButton

Private Sub ButtonGroup_Click()
    MsgBox "Hello from " & ButtonGroup.Name
End Sub

2) Now change the userform1 code to be

Dim Buttons() As New Class1

Private Sub UserForm_Initialize()
  Dim i As Long, MyCmd As Control, Topper As Long, Lefter As Long
  Dim ButtonCount As Integer, ctl As Control
  ButtonCount = 1
  Lefter = 1
  Topper = 1
  
  For i = 1 To Sheets.Count
    Set MyCmd = UserForm1.Controls.Add(bstrprogID:="Forms.CommandButton.1", Name:=Sheets(i).Name, Visible:=True)
    MyCmd.Top = Topper
    MyCmd.Left = Lefter
    MyCmd.Caption = Sheets(i).Name
    Lefter = Lefter + 80
    If Lefter > 440 Then
      Topper = Topper + 30
      Lefter = 1
    End If
  Next i
  
  For Each ctl In UserForm1.Controls
    If TypeName(ctl) = "CommandButton" Then
      ButtonCount = ButtonCount + 1
      ReDim Preserve Buttons(1 To ButtonCount)
      Set Buttons(ButtonCount).ButtonGroup = ctl
    End If
  Next ctl
End Sub

Note the positioning of the Buttons() before the initialising code.

Now when you press a button you should bring up a message box.

HTH

rylo

rylo,
Thanks for everything. That was exactly what I wanted. I really appreciate your efforts on this.

Thanks again,
Bob

Not sure if this should be in a new thread or in this one. I took the code that rylo provided and used that perfectly. What I am trying to do is as follows: After the form is populated with the command buttons and the user presses the button sheet1. I have a routine that takes that sheet1 name value and holds it to be referenced later.

Sub GetCmdCaption()
  CmdBtn = ButtonGroup.Name
 Select Case CmdBtn

        Case CmdBtn
          
            frmMain_Menu.Hide
            UserForm1.Show
    End Select
End Sub

I then use that value on Userform1 to tell it what sheet in the workbook to look at. What I would like to have happen is depending on the button on the main menu that is clicked the next form would auto populate option buttons with non repeating values found in F2 for the option name and caption. For Example in the sheets F2 holds values like belt, chain, valve, misc, etc. The code below is what I have come up with based on a deriviative of rylo code and other refrences from this forum, but I have hit a wall and I am hoping that you all might be able to help. The lines of code in red are where I have lost myself...and I am pretty sure where my biggest hangup is.
Example:

Dim Options() As New Class2
Private Sub UserForm_Initialize()
  Dim lr As Variant
  Dim Wks As Worksheet
  Dim Rng As Range
  Dim rngData As Range
  Dim rngCell As Range

  Set Wks = Worksheets(CmdBtn)
    Set Rng = Wks.Range("F2")
  Dim colWords As Collection
  Dim vntWord As Variant
lr = Range("F" & Rows.Count).End(xlUp).Row
Set colWords = New Collection
Set rngData = Range("F2:F" & lr)

For Each rngCell In rngData.Cells
           
NextDim i As Long, MyCmd As Control, Topper As Long, Lefter As Long, widther As Long, heighter As Long
  
  Dim OptCount As Integer, ctl As Control
  OptCount = 1
  Lefter = 10
  Topper = 3
  widther = 100
  heighter = 25
  For i = 1 To lr
    Set MyOpt = UserForm2.Controls.Add(bstrprogID:="Forms.OptionButton.1", Name:=lr(i).Cell, Visible:=True)    MyOpt.Top = Topper
    MyOpt.Left = Lefter
    MyOpt.Caption = lr(i).Name
    MyOpt.Width = widther
    MyOpt.Height = heighter
    
    Lefter = Lefter + 125
    If Lefter > 440 Then
      Topper = Topper + 30
      Lefter = 10
    End If
  Next i
  
  For Each ctl In UserForm2.Controls
    If TypeName(ctl) = "OptionButton" Then
      OptCount = OptCount + 1
      ReDim Preserve Options(1 To OptCount)
      Set Options(OptCount).OptionGroup = ctl
    End If
  Next ctl
End Sub

Class Module:

Public WithEvents OptionGroup As msforms.OptionButton
Private Sub OptionGroup_Click()
    MsgBox "Hello from " & OptionGroup.Caption
       
End Sub

Thanks in advance for your help.
Bob

Bob

This is getting complicated, so how about attaching an example workbook that has all the necessary parts (forms, 3-4 sheets, code) and a bit of explanation on what is to happen.

rylo

rylo,
Thanks for the reply....I agree this is getting complicated. I am attaching the entire work book. So everyone can see what I am doing. A few notes that I need to mention first. In the workbook I have two dummy forms that I was using to test code. They are userform1 and userform2. In the Class1 module I have commented out the actual 2nd form that is supposed to load. If you look at the code in frmParts you will see that I intially had this form manually poplulated with option buttons and went through routines to populate the rest of the forms. If you want to run through the program that way just change the commenting in Class1 module. Bottom line I am trying to build this application as self suficent as possible. I hope that I am conveying what it is I am looking for clearly...if not please let me know so I can try to clarify any questions. Thanks again for you interest and believe me I am expanding my VB knowledge base with this one and it has alot to do with the help from everyone here.

Thanks again,
Bob

Bob

Started working through it and it ended up getting into a loop. It opens userform1, then while actioning it, it again tries to open userform1.

Can you please document the stages that you want this to take, what should happen when, where the data comes from, what form should open, what should be completed from where etc For example

1) Open storeroom..., form frmMain_Menu opens, select Pearson.
2).....

rylo

rylo,
The same thing happened to me with the code I had in userform1. I am pretty sure that I have that all screwed up. It might be better if you change the .show command in the class1 module reference frmparts instead of userform1. That will show you how I want to the program to work in the end. But if possible I want to auto populate the frmParts form to allow for the addition of parts not yet know.

Public WithEvents ButtonGroup As msforms.CommandButton
Private Sub ButtonGroup_Click()
    'MsgBox "Hello from " & ButtonGroup.Name
    Call GetCmdCaption
    
End Sub
Sub GetCmdCaption()
  CmdBtn = ButtonGroup.Name
 Select Case CmdBtn

        Case CmdBtn
          
            frmMain_Menu.Hide
            frmParts.Show
    End Select
End Sub

1) Open storeroom..., form frmMain_Menu opens, select Pearson, frmParts opens
2) click cylinder, click search, frmSearch_Results opens
3)click the 1st item in the list box B-5-GE, frmDetail_Results opens

I hope this is what you were looking for and again thanks for all your help.

Bob

Bob

Currently frmparts has 11 distinct part types, with one generic "all parts". However, if you look at sheet Pearson, there are 15 distinct parts.

So you would like frmparts to be created in the same way that frmMain_Menu is generated only with option buttons, and in a group. The getoptioncaption code is run.

Is that right???

rylo

rylo,
Yes that is exactly what I want to do. Sorry if I made that confusing....my brain is a little tired from working on this the last few days.

Thanks again,
Bob

Bob

See if this is heading in the right direction.

rylo

rylo,
This is fantastic! I do have one question however. On the frmParts form I have a Go Back! command button. When I click that button it goes back to frmMain_Menu. If the I select a different menu the frmParts is populated with the first selection. For example from frmMain_Menu I select Pearson. It populates frmParts with the option buttons. I want to look for another part so I go back to frmMain_menu and select FPX the frmParts still shows option buttons from the first selection of Pearson. Is there some sort of clear or reset that needs to be performed so it will repopulate off of new selection. Thanks again for your long efforts on this. I really appreciate it.

Bob

Bob

Change the code for the go back button to be

Private Sub cmdBack_Click()
Unload Me
frmMain_Menu.Show
End Sub

If you unload the form, rather than just hide it, it will have to be repopulated when it is loaded again.

There are a couple of other things. I made the assumption that there would be a description for each item. However that doesn't seem to hold. Change the line

Set Rng = DataSh.Range(DataSh.Range("F2"), DataSh.Range("F2").End(xlDown))

to

Set Rng = DataSh.Range(DataSh.Range("F2"), DataSh.Cells(Rows.Count, "F").End(xlUp))

At least that will cover all the ones that have a description.

I've also not covered the selection for the all parts. You will have to enhance for that possibility.

rylo

rylo,
I really appreciate what you have done for me. It works fantastic!!!! I modified the Class2 Module to perform the All Parts Search.

Public WithEvents OptionGroup As msforms.OptionButton
Private Sub OptionGroup_Click()
   Call GetOptionCaption
End Sub

Sub GetOptionCaption()
If OptionGroup.Caption = "All Parts" Then
      OptBtn = "*"
Else
    OptBtn = OptionGroup.Caption
End If

End Sub

If I am ever in your neck of the woods I defiantly will have to buy you a Beer !
Thanks again for all your help. I can say that this thread is solved for sure this time.

Bob