Enable ActiveX control in protected worksheet

[badawio]

I am stuck! I have a worksheet that is protected. However, I have an ActiveX control button that I want users to be able to use. I have tried building in the unprotect functionality into the ActiveX control as follows:

Sub PrefSelection()
'
' PrefSelection Macro
'

'
Sheet13.Unprotect Password:="password"
(my code)
Sheet13.Protect Password:="abqaiq1!"
End Sub

But no luck. I have also tried building it into ThisWorksheet as a start event as follows:

Private Sub Workbook_Open()
Welcome.Show
Sheets(13).Protect Password:="password", _
UserInterFaceOnly:=True
End Sub

I have a Spash Screen thus the "Welcome.Show" coding. Unfortunately this does not work either. The macro appears to work but I still get the error indicating that the spreadsheet is protected when I try and run the ActiveX control.

I'm considering putting in a control box to unlock the sheet that the user has to push before making an ActiveX selection. It is really sloppy though.

Any ideas??

Thanks,
Omar

 

[Horologium]

Hi Badawio,

I actually meet the same problem as yours.
Did you find any solution ?

Thanks,
H

 

[lagaths]

Hi Friends,

In my excel i made to pick the date by calendar. (Active x controls)
Work sheet is protected and this particular cell range is made to unlock by password 3333.
When I click the cell, calendar pop up and when i select the date following error appears.
Please help me on this.

"Run-time error '1004':


Unable to set the Numberformat properly of the Range class"

Existing Code as follows..

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "dd mmmm yyyy"
ActiveCell.Select
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A7:A100,B2,B1"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub

 

[Andrew500]

Hi,
Did anyone have an answer for this one? I have the same problem. I have an ActiveX Combobox on a worksheet with drives same data tables and charts. I use Sheets("sheetname").Protect Password:="pwd", UserInterFaceOnly:=True when opening the wbook, but when I select the activeX box to try change it I get the message "the cell or chart that you are trying to change is protected and therefore read-only. ...". Everything I look for says set the UserInterfaceOnly to true but this isn't doing what I want. Is this an ActiveX thing? and is there a known solution? Any help appreciated.

 

[Andrew Poulsom]

If your ComboBox has a linked cell you need to unlock that cell (before protecting the worksheet).

 

[Andrew500]

Woohoo. That did the trick. Thanks for responding so quickly.