Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error with vba and checkbox
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error with vba and checkbox
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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error with vba and checkbox
"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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error with vba and checkbox
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- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error with vba and checkbox
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- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error with vba and checkbox
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error with vba and checkbox
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- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error with vba and checkbox
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error with vba and checkbox
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- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error with vba and checkbox
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checkbox in runtime | Excel Programming | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Interior class error/runtime error 1004 | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming | |||
Unknown where is the problem on the Runtime error - Automation error | Excel Programming |