You are here:

Excel/Combo Box Not Working Now

Advertisement


Question
Hi Jerry,

You have been most helpful finding solutions for me. I used the code you sent me to unprotect and then re-protect after the code enters today's date. Thank you for that. I am now running into an issue with my ComboBox (drop down list for names that enters the name in that same cell). Once the sheet is protected, the ComboBox no longer works (it reverts back to the tiny font it had before I changed the data validation list to a ComboBox). It displays the "spinning Circle" icon like it is working, but the ComboBox Drop Drop List never appears. I included all the Code below. Thank you for any assistance.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("H:H")) Is Nothing Then
Me.Unprotect "Password"
Target.Offset(0, 1).Value = Format(Date, "mm/dd/yy")
Me.Protect "Password"
End If
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
 Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("ValidationLists")

Set cboTemp = ws.OLEObjects("TempCombo")
 On Error Resume Next
 With cboTemp
 'clear and hide the combo box
   .ListFillRange = ""
   .LinkedCell = ""
   .Visible = False
 End With
On Error GoTo errHandler
 If Target.Validation.Type = 3 Then
   'if the cell contains a data validation list
   Cancel = True
   Application.EnableEvents = False
   'get the data validation formula
   str = Target.Validation.Formula1
   str = Right(str, Len(str) - 1)
   With cboTemp
     'show the combobox with the list
     .Visible = True
     .Left = Target.Left
     .Top = Target.Top
     .Width = Target.Width + 5
     .Height = Target.Height + 5
     .ListFillRange = str
     .LinkedCell = Target.Address
   End With
   cboTemp.Activate
   'open the drop down list automatically
   Me.TempCombo.DropDown

 End If
 
errHandler:
 Application.EnableEvents = True
 Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

If Application.CutCopyMode Then
 'allow copying and pasting on the worksheet
 GoTo errHandler
End If

Set cboTemp = ws.OLEObjects("TempCombo")
 On Error Resume Next
 With cboTemp
   .Top = 10
   .Left = 10
   .Width = 0
   .ListFillRange = ""
   .LinkedCell = ""
   .Visible = False
   .Value = ""
 End With

errHandler:
 Application.EnableEvents = True
 Exit Sub

End Sub '====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp

Private Sub TempCombo_KeyDown(ByVal _
       KeyCode As MSForms.ReturnInteger, _
       ByVal Shift As Integer)
   Select Case KeyCode
       Case 9 'Tab
         ActiveCell.Offset(0, 1).Activate
       Case 13 'Enter
         ActiveCell.Offset(1, 0).Activate
       Case Else
         'do nothing
   End Select
End Sub
'====================================

Answer
When you protect a sheet, there are a lot of settings you can add.  One of them is actually a HIDDEN parameter called UserInterfaceOnly.  If you add this to your PROTECT commands, it will basically protect the sheet from humans, but leave the sheet free for VBA to act on.


Me.Protect "Password", UserInterfaceOnly:=True


As a hidden feature, it's only on when you JUST turned it on.  If you close the workbook and reopen it, that flag is off until you turn it on again.  As such, you might want to add a Worksheet_Activate macro to that sheet module that turns it on, in case you need that right away.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.