Creating a check box that does not require security clearance.
 

I'm trying to create a simple check box that returns a value into a cell (for
instance A1). I create the check box and put A1 in the "LinkedCell" section
under Properties. This places a TRUE or FALSE into A1. Now every time I open
the spreadsheet I have to chose to enable macros. If I share this file whit
others who have high security settings, they won't even be able to open it.
This file is going to unsophisticated users and this simple check box may
make it so that they can't even use the file. Is there a way to fix it so
that this isn't seen as a "custom" macro?

If I create a check box but have the value of the check box appear nowhere
on the sheet, the security does not bother it.

Thanks.

Use the checkbox from the forms toolbar instead


Regards,

Peo Sjoblom

"Maverick2U" wrote:

I'm trying to create a simple check box that returns a value into a cell (for
instance A1). I create the check box and put A1 in the "LinkedCell" section
under Properties. This places a TRUE or FALSE into A1. Now every time I open
the spreadsheet I have to chose to enable macros. If I share this file whit
others who have high security settings, they won't even be able to open it.
This file is going to unsophisticated users and this simple check box may
make it so that they can't even use the file. Is there a way to fix it so
that this isn't seen as a "custom" macro?

If I create a check box but have the value of the check box appear nowhere
on the sheet, the security does not bother it.

Thanks.

Peo,

That's what I did. I created the check box and then edited it's properties
so that I could tell it where to place the value so that I could create a
formula based on it's results. As soon as I change any of the properites of
the check box, security views it as a "custom" macro.

Thanks,
Dean

"Peo Sjoblom" wrote:

Use the checkbox from the forms toolbar instead


Regards,

Peo Sjoblom

"Maverick2U" wrote:

I'm trying to create a simple check box that returns a value into a cell (for
instance A1). I create the check box and put A1 in the "LinkedCell" section
under Properties. This places a TRUE or FALSE into A1. Now every time I open
the spreadsheet I have to chose to enable macros. If I share this file whit
others who have high security settings, they won't even be able to open it.
This file is going to unsophisticated users and this simple check box may
make it so that they can't even use the file. Is there a way to fix it so
that this isn't seen as a "custom" macro?

If I create a check box but have the value of the check box appear nowhere
on the sheet, the security does not bother it.

Thanks.

I can't reproduce this with either checkbox (control toolbox or forms toolbar)
Since you say properties it sounds as though you are using the check box
from the
control toolbox (in the forms toolbar you use format control to set the cell
link)
An uneducated guess is that you might have selected view code once, that is
enough to automatically add these lines (even if you just exit out from there)

Private Sub CheckBox1_Click()

End Sub

thus will trigger "Enable Macro" upon opening, to get rid of it, go into
design mode and right click and select view code and delete the text or press
Alt + F11 and double click the sheet name in the project pane to the left,
then delete the text.

If indeed you are using the check box from the forms toolbar then you must
mistakenly have created a macro or linked the check box to a macro (right
click assign macro)

There is no other way that it would trigger the macro security. It is
definitely easier to mistakenly create code using the firts method (view
code)


Best Regards,

Peo Sjoblom



"Maverick2U" wrote:

Peo,

That's what I did. I created the check box and then edited it's properties
so that I could tell it where to place the value so that I could create a
formula based on it's results. As soon as I change any of the properites of
the check box, security views it as a "custom" macro.

Thanks,
Dean

"Peo Sjoblom" wrote:

Use the checkbox from the forms toolbar instead


Regards,

Peo Sjoblom

"Maverick2U" wrote:

I'm trying to create a simple check box that returns a value into a cell (for
instance A1). I create the check box and put A1 in the "LinkedCell" section
under Properties. This places a TRUE or FALSE into A1. Now every time I open
the spreadsheet I have to chose to enable macros. If I share this file whit
others who have high security settings, they won't even be able to open it.
This file is going to unsophisticated users and this simple check box may
make it so that they can't even use the file. Is there a way to fix it so
that this isn't seen as a "custom" macro?

If I create a check box but have the value of the check box appear nowhere
on the sheet, the security does not bother it.

Thanks.

Poe,

Thaks a lot. That did the trick!!!!

How can something so simple be soooooo complicated?

Thanks again,
Dean

"Peo Sjoblom" wrote:

I can't reproduce this with either checkbox (control toolbox or forms toolbar)
Since you say properties it sounds as though you are using the check box
from the
control toolbox (in the forms toolbar you use format control to set the cell
link)
An uneducated guess is that you might have selected view code once, that is
enough to automatically add these lines (even if you just exit out from there)

Private Sub CheckBox1_Click()

End Sub

thus will trigger "Enable Macro" upon opening, to get rid of it, go into
design mode and right click and select view code and delete the text or press
Alt + F11 and double click the sheet name in the project pane to the left,
then delete the text.

If indeed you are using the check box from the forms toolbar then you must
mistakenly have created a macro or linked the check box to a macro (right
click assign macro)

There is no other way that it would trigger the macro security. It is
definitely easier to mistakenly create code using the firts method (view
code)


Best Regards,

Peo Sjoblom



"Maverick2U" wrote:

Peo,

That's what I did. I created the check box and then edited it's properties
so that I could tell it where to place the value so that I could create a
formula based on it's results. As soon as I change any of the properites of
the check box, security views it as a "custom" macro.

Thanks,
Dean

"Peo Sjoblom" wrote:

Use the checkbox from the forms toolbar instead


Regards,

Peo Sjoblom

"Maverick2U" wrote:

I'm trying to create a simple check box that returns a value into a cell (for
instance A1). I create the check box and put A1 in the "LinkedCell" section
under Properties. This places a TRUE or FALSE into A1. Now every time I open
the spreadsheet I have to chose to enable macros. If I share this file whit
others who have high security settings, they won't even be able to open it.
This file is going to unsophisticated users and this simple check box may
make it so that they can't even use the file. Is there a way to fix it so
that this isn't seen as a "custom" macro?

If I create a check box but have the value of the check box appear nowhere
on the sheet, the security does not bother it.

Thanks.

Poe,

You did such a great job answering my last question that I thought I'd throw
you a follow-up question. I need to make this change to three check boxes in
three worksheets within the workbook. On top of that, I need to change this
in 96 workbooks as I used one file as a template and didn't realize the
problem until after I was done. Is there a quick and easy way to do this?

Thanks again,
Dean

"Peo Sjoblom" wrote:

I can't reproduce this with either checkbox (control toolbox or forms toolbar)
Since you say properties it sounds as though you are using the check box
from the
control toolbox (in the forms toolbar you use format control to set the cell
link)
An uneducated guess is that you might have selected view code once, that is
enough to automatically add these lines (even if you just exit out from there)

Private Sub CheckBox1_Click()

End Sub

thus will trigger "Enable Macro" upon opening, to get rid of it, go into
design mode and right click and select view code and delete the text or press
Alt + F11 and double click the sheet name in the project pane to the left,
then delete the text.

If indeed you are using the check box from the forms toolbar then you must
mistakenly have created a macro or linked the check box to a macro (right
click assign macro)

There is no other way that it would trigger the macro security. It is
definitely easier to mistakenly create code using the firts method (view
code)


Best Regards,

Peo Sjoblom



"Maverick2U" wrote:

Peo,

That's what I did. I created the check box and then edited it's properties
so that I could tell it where to place the value so that I could create a
formula based on it's results. As soon as I change any of the properites of
the check box, security views it as a "custom" macro.

Thanks,
Dean

"Peo Sjoblom" wrote:

Use the checkbox from the forms toolbar instead


Regards,

Peo Sjoblom

"Maverick2U" wrote:

I'm trying to create a simple check box that returns a value into a cell (for
instance A1). I create the check box and put A1 in the "LinkedCell" section
under Properties. This places a TRUE or FALSE into A1. Now every time I open
the spreadsheet I have to chose to enable macros. If I share this file whit
others who have high security settings, they won't even be able to open it.
This file is going to unsophisticated users and this simple check box may
make it so that they can't even use the file. Is there a way to fix it so
that this isn't seen as a "custom" macro?

If I create a check box but have the value of the check box appear nowhere
on the sheet, the security does not bother it.

Thanks.

First, this is kind of dangerous.

So I think I'd copy all 96 workbooks into their own folder and work against
copies of the originals.

If something goes really wrong, you don't want to recreate all 96 workbooks???

Then try something like:

Option Explicit
Sub testme01()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Application.EnableEvents = False
Set wkbk = Workbooks.Open(Filename:=myPath & myFiles(fCtr))
Application.EnableEvents = True
Call DeleteAllVBA(wkbk)
wkbk.Close savechanges:=True
Next fCtr
End If

End Sub
Sub DeleteAllVBA(wkbk As Workbook)

Dim VBComp As Object
Dim VBComps As Object
Const vbext_ct_StdModule As Long = 1
Const vbext_ct_MSForm As Long = 3
Const vbext_ct_ClassModule As Long = 2

Set VBComps = wkbk.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

The deleteallVBA macro was stolen from Chip Pearson's web site:
http://cpearson.com/excel/vbe.htm
(and slightly modified to use late binding.)


Be careful.

Don't run this against a folder that contains workbooks whose macros should not
be destroyed!




Maverick2U wrote:

Poe,

You did such a great job answering my last question that I thought I'd throw
you a follow-up question. I need to make this change to three check boxes in
three worksheets within the workbook. On top of that, I need to change this
in 96 workbooks as I used one file as a template and didn't realize the
problem until after I was done. Is there a quick and easy way to do this?

Thanks again,
Dean

"Peo Sjoblom" wrote:

I can't reproduce this with either checkbox (control toolbox or forms toolbar)
Since you say properties it sounds as though you are using the check box
from the
control toolbox (in the forms toolbar you use format control to set the cell
link)
An uneducated guess is that you might have selected view code once, that is
enough to automatically add these lines (even if you just exit out from there)

Private Sub CheckBox1_Click()

End Sub

thus will trigger "Enable Macro" upon opening, to get rid of it, go into
design mode and right click and select view code and delete the text or press
Alt + F11 and double click the sheet name in the project pane to the left,
then delete the text.

If indeed you are using the check box from the forms toolbar then you must
mistakenly have created a macro or linked the check box to a macro (right
click assign macro)

There is no other way that it would trigger the macro security. It is
definitely easier to mistakenly create code using the firts method (view
code)


Best Regards,

Peo Sjoblom



"Maverick2U" wrote:

Peo,

That's what I did. I created the check box and then edited it's properties
so that I could tell it where to place the value so that I could create a
formula based on it's results. As soon as I change any of the properites of
the check box, security views it as a "custom" macro.

Thanks,
Dean

"Peo Sjoblom" wrote:

Use the checkbox from the forms toolbar instead


Regards,

Peo Sjoblom

"Maverick2U" wrote:

I'm trying to create a simple check box that returns a value into a cell (for
instance A1). I create the check box and put A1 in the "LinkedCell" section
under Properties. This places a TRUE or FALSE into A1. Now every time I open
the spreadsheet I have to chose to enable macros. If I share this file whit
others who have high security settings, they won't even be able to open it.
This file is going to unsophisticated users and this simple check box may
make it so that they can't even use the file. Is there a way to fix it so
that this isn't seen as a "custom" macro?

If I create a check box but have the value of the check box appear nowhere
on the sheet, the security does not bother it.

Thanks.

--

Dave Peterson