Combo boxes in Excel 2007 (Excel 2003 SP3)

[steveh]

Good morning

I have a workbook that has to be unlocked by code and then re-locked after because of the formulas. In all instances of 2003 the Combo works fine but everybody who uses 2007 are saying that the Combo does not drop down. They assure me that they have followed my instructions for setting up a trusted folder and accepting my self cert and they all say that they have done it.

Does anybody know if there is a known problem and if so a known cure?

[HansV]

Do you "lock" the workbook as a whole, or individual worksheets? If the latter, it is not necessary to unlock and re-lock: you can protect the sheets using code:

Dim wsh As Worksheet
Set wsh = ...
wsh.Protect Password:="secret", UserInterfaceOnly:=True

After that, you can manipulate the worksheet in code without unprotecting and reprotecting it.

What kind of combo box are you talking about? Data | Validation, or a combo box from the Forms toolbar or a combo box from the Control Toolbox?

[steveh]

Hi Hans

Thank you for the prompt response. Each WS is locked individually, the one in question with the Combo (Control Toolbox) is unlocked when A button is pressed as follows

Code: Select all

Public Sub UnProtectMe()
  Dim oSht As Worksheet
  Dim oQt As QueryTable
  For Each oSht In Worksheets
    oSht.Unprotect "password"
    For Each oQt In oSht.QueryTables
      oQt.BackgroundQuery = False
    Next oQt
  Next oSht

  ActiveWorkbook.RefreshAll

  For Each oSht In Worksheets
    oSht.Protect "password"
  Next oSht
End Sub

Thanks

[HansV]

I suspect that, despite what they say, they haven't correctly set up the workbook to be trusted.