Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    Sacramento, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB code/macro security settings and tabbing (Excel 2000)

    I've created an Excel spreadsheet with a list box and text boxes and VB code running to automatically fill the text boxes when the user chooses an item from the list box. It works great when the macro security setting is medium or low, but it won't work when the macro security setting is set to high.

    I believe the default macro security setting for Excel 2000 is high. Is there any way to make my code run without changing this?

    My intention is to distribute this spreadsheet to a few hundred potential users, and I don't want them to have to change their macro security settings to use the spreadsheet as it was intended. (The users are not part of my company, so I have no control over this.)

    Also, I am protecting the worksheet containing the list box so that only certain cells can be edited. How can I get the <Tab> key on the keyboard to tab from an editable cell to the list box?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    There is no way to change the macro security settings from within a macro. This is by design, otherwise any macro virus would be able to turn off macro security...

    You can sign your workbook with a digital certificate; users would still have to confirm that they trust the certificate once.

  3. #3
    New Lounger
    Join Date
    Oct 2004
    Location
    Sacramento, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    Thanks Hans. Any suggestions for using the <Tab> key to navigate to the list box -- versus having to click on it?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    No, I don't know how to trap the Tab key; Excel does not expose a KeyDown or KeyPress event for a worksheet.

  5. #5
    New Lounger
    Join Date
    Oct 2004
    Location
    Sacramento, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    Thanks Hans!

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    If the listbox you are using came from the Control Tool Box, and if it is named Listbox1, then you can place the code below in the worksheet selection change event routine. Then if a tab would take you to cell E7, the listbox will be activated.

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("E7")) Is Nothing Then
    Application.EnableEvents = False
    ListBox1.Activate
    Application.EnableEvents = True
    End If
    End Sub
    </pre>

    Legare Coleman

  7. #7
    New Lounger
    Join Date
    Oct 2004
    Location
    Sacramento, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    Thanks very much, Legare!

    I can now use the tab key to navigate to the list box, but I'm unable to use the tab key to navigate off of the list box. Am I missing something? (Sorry--I'm still learning!)

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    No, you are not missing anything. I haven't actually tried it, but I think that you could put code into the listbox key down event that would check to see if the key was the tab, and if it is then select a cell on the worksheet. If you need help doing that let us know.
    Legare Coleman

  9. #9
    New Lounger
    Join Date
    Oct 2004
    Location
    Sacramento, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    Yes, please! Here's how I was starting this -- should I reset the focus using the tab key event (if so, how do I reference a specific cell in the spreadsheet?), or is there a way to code so that it tabs to the next available cell?

    Private Sub Agency_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 9 Then
    ??????
    End If
    End Sub

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    Use the keyup not the keydown, if you use the keydown, the listbox gets the focus again.

    Something like this: (change the "goto" cell as desired)

    Private Sub Agency_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 9 Then Range("g1").Activate
    End Sub

    Steve

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    I would do this:

    <pre>Private Sub Agency_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Application.EnableEvents = False
    If KeyCode = 9 Then
    Range("g1").Select
    End If
    Application.EnableEvents = True
    End Sub</pre>

    Legare Coleman

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    Good point, especially with the possibility of a SelectionChange macro.

    Steve

  13. #13
    New Lounger
    Join Date
    Oct 2004
    Location
    Sacramento, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code/macro security settings and tabbing (Excel 2000)

    Now I'm cooking with fire!

    I ended up using the Keydown event. When I used Keyup and tabbed into the listbox, the listbox activated, but the target cell in the Keyup sub also activated--from a user's perspective, it appeared as though tabbing skipped the listbox. Using the Keydown gave me what I wanted - I can tab into the listbox, select from the list and tab out of it into the next cell.

    Thanks Legare and Steve!

Posting Permissions

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