Runtime error with vba and checkbox

Heyas,

Hokay, noob behind the keyboard here and I have a situation I have no
clue on how to fix. I have only had minimal training in visual basic.
So any explanations in monosyllabic terms will be greatly appreciated.
Situation: I'm making a spreadsheet and want to limit access by users
to change data. I have a button that will generate data in the
necessary fields. I have an checkbox that when selected should disable
the commandbutton from functioning as well as locking the fields that
had information generated in them.
Here is the catch. When the sheet is unprotected the button and
checkbox do not lock the data fields or diable the command button, but
no error. But when I turn on the protection, I get an error '1004'
Unable to set the Locked property of the range class when I click on
the checkbox every time.

Here is the code:

Private Sub CheckBox1_Click()
' chkLockAttribs_Click()
ActiveWindow.ScrollRow = 8

Sheets("Stats").Range("C18").Select
If CheckBox1.Enabled = True Then
Sheets("Stats").Range("C18, D19:D23, D25, D26:F26, J19:J23,
P33, P35").Select
Selection.Locked = True
CommandButton1.Visible = False
Else
Sheets("Stats").Range("C18, D19:D23, D25, D26:F26, J19:J23,
P33, P35").Select
Selection.Locked = False
CommandButton1.Visible = True
End If

Sheets("Stats").Range("D19").Select
End Sub


Any help would be greatly appreciated. Thanks.

-SplatterKat-

It kind of sounds like you have a linkedcell for the checkbox in a locked cell
on that protected worksheet. Is that possible?

Remember the "lockedness" of a cell doesn't really do much until the worksheet
is protected.

This routine looks at the value of the checkbox (true = clicked) and does things
based on that.

I'm not sure if does what you really want, but it may be a start:

Option Explicit
Private Sub CheckBox1_Click()

Dim myRng As Range
Dim myPwd As String

myPwd = "secret"

Set myRng = Me.Range("C18, D19:D23, D25, D26:F26, J19:J23, P33, P35")

Me.Unprotect Password:=myPwd
myRng.Locked = Me.CheckBox1.Value
Me.CommandButton1.Visible = Not (Me.CheckBox1.Value)
Me.Protect Password:=myPwd

End Sub


I am guessing that all this stuff is on the Stats worksheet. I removed the
..select and the references to the worksheet name--I used Me. instead. Me.
refers to the thing holding the code--in this case the Stats worksheet.



SplatterKat wrote:
>
> Heyas,
>
> Hokay, noob behind the keyboard here and I have a situation I have no
> clue on how to fix. I have only had minimal training in visual basic.
> So any explanations in monosyllabic terms will be greatly appreciated.
> Situation: I'm making a spreadsheet and want to limit access by users
> to change data. I have a button that will generate data in the
> necessary fields. I have an checkbox that when selected should disable
> the commandbutton from functioning as well as locking the fields that
> had information generated in them.
> Here is the catch. When the sheet is unprotected the button and
> checkbox do not lock the data fields or diable the command button, but
> no error. But when I turn on the protection, I get an error '1004'
> Unable to set the Locked property of the range class when I click on
> the checkbox every time.
>
> Here is the code:
>
> Private Sub CheckBox1_Click()
> ' chkLockAttribs_Click()
> ActiveWindow.ScrollRow = 8
>
> Sheets("Stats").Range("C18").Select
> If CheckBox1.Enabled = True Then
> Sheets("Stats").Range("C18, D19:D23, D25, D26:F26, J19:J23,
> P33, P35").Select
> Selection.Locked = True
> CommandButton1.Visible = False
> Else
> Sheets("Stats").Range("C18, D19:D23, D25, D26:F26, J19:J23,
> P33, P35").Select
> Selection.Locked = False
> CommandButton1.Visible = True
> End If
>
> Sheets("Stats").Range("D19").Select
> End Sub
>
> Any help would be greatly appreciated. Thanks.
>
> -SplatterKat-

--

Dave Peterson

I tried the ol copy/paste and this didn't work either. The new and
improved error states:

Compile error. Invalid inside proceedure. Had the Option explicit
highlighted. I also checked the boxes that the checkbox and button are
connected with. Neither are locked but they are hidden. Unhiding them
didn't make any difference though.

I do appreciate the help, but it is still broke. Any other ideas?

-Robert Lizak-

"Option Explicit" goes at the top of the module.

It's a signal to the compiler that you want to be forced to declare your
variables.

You can either move it to the top of the module or delete that line.

The code I suggested didn't rely on a linkedcell. Do you need linked cells for
anything?

SplatterKat wrote:
>
> I tried the ol copy/paste and this didn't work either. The new and
> improved error states:
>
> Compile error. Invalid inside proceedure. Had the Option explicit
> highlighted. I also checked the boxes that the checkbox and button are
> connected with. Neither are locked but they are hidden. Unhiding them
> didn't make any difference though.
>
> I do appreciate the help, but it is still broke. Any other ideas?
>
> -Robert Lizak-

--

Dave Peterson

Nope. I was just using it as a placeholder really. Something I would
be able to change with formulas in excel should I need to later on.
It's a work in progress thing.

-Robert Lizak-

> Option Explicit
> Private Sub CheckBox1_Click()
>
> Dim myRng As Range
> Dim myPwd As String
>
> myPwd = "secret"
>
> Set myRng = Me.Range("C18, D19:D23, D25, D26:F26, J19:J23, P33, P35")
>
> Me.Unprotect Password:=myPwd
> myRng.Locked = Me.CheckBox1.Value
> Me.CommandButton1.Visible = Not (Me.CheckBox1.Value)
> Me.Protect Password:=myPwd
>
> End Sub

Didn't work on my excel 2000 edition. The "Me." comes up highlighted
in the debugging mode. Is there another way to do this script that
would be more friendly to older versions of excel? Most of the other
people who will be using the spreadsheet will have the more outdated
versions as well. Much appreciated with all the help. Thanks.

-Robert Lizak-
aka -SplatterKat-

I thought you used the checkbox from the control toolbox toolbar and placed it
on a worksheet.

If you did, then this code is under the worksheet that holds that checkbox. And
the Me. refers to that worksheet.

If you used a checkbox from the Forms toolbar, try changing all the "Me."'s to
"Activesheet." (include the trailing dot).

SplatterKat wrote:
>
> > Option Explicit
> > Private Sub CheckBox1_Click()
> >
> > Dim myRng As Range
> > Dim myPwd As String
> >
> > myPwd = "secret"
> >
> > Set myRng = Me.Range("C18, D19:D23, D25, D26:F26, J19:J23, P33, P35")
> >
> > Me.Unprotect Password:=myPwd
> > myRng.Locked = Me.CheckBox1.Value
> > Me.CommandButton1.Visible = Not (Me.CheckBox1.Value)
> > Me.Protect Password:=myPwd
> >
> > End Sub
>
> Didn't work on my excel 2000 edition. The "Me." comes up highlighted
> in the debugging mode. Is there another way to do this script that
> would be more friendly to older versions of excel? Most of the other
> people who will be using the spreadsheet will have the more outdated
> versions as well. Much appreciated with all the help. Thanks.
>
> -Robert Lizak-
> aka -SplatterKat-

--

Dave Peterson

Dave Peterson wrote:
> I thought you used the checkbox from the control toolbox toolbar and placed it
> on a worksheet.
>
> If you did, then this code is under the worksheet that holds that checkbox. And
> the Me. refers to that worksheet.
>
> If you used a checkbox from the Forms toolbar, try changing all the "Me."'s to
> "Activesheet." (include the trailing dot).

Ok, I changed them out and am still getting an error.
Object doesn't support this property or method
with the following line highlighted:

myRng.Locked = ActiveSheet.CheckBox1.Value

I do apprecioate the help you have given.

-Robert Lizak-

next try...

myRng.Locked = ActiveSheet.oleobjects("CheckBox1").object.Value

SplatterKat wrote:
>
> Dave Peterson wrote:
> > I thought you used the checkbox from the control toolbox toolbar and placed it
> > on a worksheet.
> >
> > If you did, then this code is under the worksheet that holds that checkbox. And
> > the Me. refers to that worksheet.
> >
> > If you used a checkbox from the Forms toolbar, try changing all the "Me."'s to
> > "Activesheet." (include the trailing dot).
>
> Ok, I changed them out and am still getting an error.
> Object doesn't support this property or method
> with the following line highlighted:
>
> myRng.Locked = ActiveSheet.CheckBox1.Value
>
> I do apprecioate the help you have given.
>
> -Robert Lizak-

--

Dave Peterson

Dave Peterson wrote:
> next try...
>
> myRng.Locked = ActiveSheet.oleobjects("CheckBox1").object.Value
>

I tried that and it came back with an error as well:

Unable to get the OLEObjects property of the worksheet class.

-Robert lizak-

Do you have the correct worksheet active?

Is the checkbox you're using from the control toolbox toolbar and is it named
"checkbox1"?

If no to any of those, then it's time to share more info (I've lost track of the
details).

And you may want to post more of the code and where it's located.


SplatterKat wrote:
>
> Dave Peterson wrote:
> > next try...
> >
> > myRng.Locked = ActiveSheet.oleobjects("CheckBox1").object.Value
> >
>
> I tried that and it came back with an error as well:
>
> Unable to get the OLEObjects property of the worksheet class.
>
> -Robert lizak-

--

Dave Peterson