Switchboard (v2000)

Topic

New Reply

I am developing an XL workbook to be used for submitting employee personnel changes. There will be two functions to the workbook…to remit information on new employees and to submit changes on current employees. Each function has several sheets associated with it. I want to have the user select a function (New Employee or Personnel Change) and depending on the selection, certain sheets would unhide. I am new to VBA in XL but am very comfortable with VBA in Access so I hope to bridge the gap. I looked for the following but couldn’t find any code samples:
* The ability to hide and unhide sheets depending on a selection
* How can I hide the code so the user cannot view it (I ask this because I tried to view the code in Microsoft’s templates but was unable to view it)
* When the user is completed, I want them to click a button and have the ‘File’-‘Send To’-‘Mail Recipient (as attachment)’ to execute
* Would simply recording a macro be my best approach?
If there are any existing templates where I can view code, that would be great but any help would be appreciated.
Thanks!!

Reply | Quote

Replies

There are many ways to get input from a user. You might try the Forms toolbar combobox (click it and then click/drag
on a worksheet to create the dropdown)
Then rt-click it >format control> In the Input range box, type something like A1:A2 (The sheet range where you will
type in :New Employee and Personnel Change)
In the Cell Link box enter, say B1 (If the user selects the first choice, Cell B1 will become 1, second choice: B1=2)
Click OK

Now create a list of sheets you want visible when the user selects 1 and a list for 2 (use sheet names). These lists
can be hidden later. Select each list and Name
the ranges: ListA and ListB

Now you need to Assign a Macro to this combobox
The macro will need to Unhide the correct sheets depending on the user’s selection. So the macro will look
something like:

Option Explicit

Sub OpenSheets()
    dim oCell as range
    If Sheets("Sheet1").Range("B1")=1 then
          For each oCell in Range("ListA")
                  Sheets(oCell).Visible=xlSheetVisible
          Next oCell
    Else
       For each oCell in Range("ListB")
                  Sheets(oCell).Visible=xlSheetVisible
          Next oCell
    End if
end Sub

To Assign this macro to your combobox, rt-click on the combobox>Assign macro and select the name of the
above macro.

You will in all likelyhood want to use the Workbook_Open event (in the special “ThisWorkbook” module to hide
all sheets in ListA and ListB:

Private Sub Workbook_Open()
Dim oCell as Range
For each oCell in Union(Range(“ListA”),Range(“ListB”))
Sheets(oCell).Visible=xlSheetHidden
Next oCell
End sub

This should help you get started.

Reply | Quote

Briefly, in order:

Look for examples of the Worksheets(n).Visible Property in this Forum and the Excel VBE Help

Code for each Workbook can be Protected and Hidden by right-clicking the Project in the VBE project window and selecting Properties

Look for examples of the SendMail Method in this Forum and the Excel VBE Help; and see Ron DeBruin’s Sendmail page.

No, recording isn’t very efficient. It tends to select each object over and over rather than Declaring and Typing and then referring to each declared variable. Use recorded Methods it to get a feel for particular Methods, but don’t rely on recorded macros.

And ask here for help.

Reply | Quote

Paul & John,
Thanks a bunch! Those are exactly the tips I needed and wasn’t finding outright.
Dashiell

Reply | Quote