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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

2) Now change the userform1 code to be

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Class Module:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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