Creating a user form and need help with VBA I think.

[brockvilleal]

I have searched many posts but could not find what I am looking for. I am using Excel 2000 with XP. I am working with two workbooks, exch with multiple worksheets. One I'll call data, the other user. In the data workbook I have my data (which will be updated yearly) and the other will have the user's specific records. From help through this forum, I was able to get my dynamic data validation to work whereby the selection in one cell populated the data in the next data validation field. I have approximately 10 such columns of data validation. What this does is get the user to select only one very specific item from a list of 800 or so. This item has a specific value associated with it

Now the fun really begins. I am trying to create a workbook that will track the users items and I need to design/build something that most users will be able to use without messing up. What I had thought of doing was creating a main menu page with command buttons and using hyperlinks from these buttons that take the user to the worksheet they are requesting. From that worksheet there would be a "main menu" button taking them back to the main page.

I could get this to work however, I believe I will need to program some VBA at some time because I will want to open the "data" workbook from the user workbook, I will want to deactivate certain menu options, create my own customer menu, move some data around behind the scene, etc. My fear is I know next to nothing about VBA. Since I will most likely need to add VBA to my user workbook, I may as well create a user form with command buttons within it.

I apologize for the long winden note, but I wanted those that could help know where I am at with my pet "project". Finally, my question. I have played a little with creating a user form. I need to know the following: How do I activate the user form upon opening the user workbook. Second, how do I add command buttons on this user form. If someone can show me how to program one command button within the userform to, when selected, jump to the uppermost left corner blank cell of another worksheet within the same workbook, I would be much appreciated. Once I see this code, I can probably configure my other command button tasks and move from there.

Thanks,

Al

 

[Smitty]

Welcome to the Board!

Here are a couple of quickies to get you started:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#007F00">'   Show the form when the WB opens</SPAN>
    UserForm1.Show
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

EDIT: That bit goes in the ThisWorkbook module, the rest goes in the UserForm's module.

For a UserForm:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#007F00">'   Dump the UserForm</SPAN>
    Unload Me
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()
    MsgBox "Now moving to the User Sheet", vbOKOnly + vbInformation, "Action"
    Application.Goto [<SPAN style="color:#007F00">'Sheet1'!A1]</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

A lot of what you want done can be accomplished with the macro recorder. Note that will add a bunch of erroneous/unecessary stuff, so post back what you get for a thorough cleaning.

Hope that helps,

Smitty

 

[brockvilleal]

Thanks Smitty

I got that to work. It of course generated more questions.

If I close the user form, how do I get it to open again without opening the workbook? Can I "lock" in the user form to appear on a specific page and location and maybe always have it on? From your code what does vbOKOnly + vbInformation mean or do?

thanks again. I'm going places now.

Al

 

[Smitty]

If I close the user form, how do I get it to open again without opening the workbook? Can I "lock" in the user form to appear on a specific page and location and maybe always have it on? From your code what does vbOKOnly + vbInformation mean or do?

1) You can call the UserForm from a control, like a button, or a worksheet event. I.E.
<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforePrint()
    UserForm1.Show
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

2) You can set your code to have the UF appear only if a particular sheet is the active sheet. To always have the UF on, you need to set its ShowModal property to False.

3) vbOKOnly means that only the "OK" button will show up on the Message Box. vbInformation brings up the Information icon on the MB. When you get to that part of the message box syntax in VBA, the Intellisense editor will show you the various options that you have.

Smitty

 

[brockvilleal]

Thanks again.

I'll do some playing try to figure some things out and when I get stuck look up the message board for answers.

Appreciate it