Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Switchboard (v2000)

    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!!

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switchboard (v2000)

    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
    <pre>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
    </pre>


    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.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Switchboard (v2000)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switchboard (v2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •