Enable ActiveX control in protected worksheet

badawio

New Member
Joined
Jun 24, 2011
Messages
1
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


 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Horologium

New Member
Joined
Mar 12, 2012
Messages
1
Hi Badawio,

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

Thanks,
H
 

lagaths

New Member
Joined
Jan 20, 2013
Messages
1
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

New Member
Joined
Aug 13, 2014
Messages
8
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If your ComboBox has a linked cell you need to unlock that cell (before protecting the worksheet).
 

Watch MrExcel Video

Forum statistics

Threads
1,099,377
Messages
5,468,256
Members
406,575
Latest member
Joe00

This Week's Hot Topics

Top