Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enableling a shape
I have a command button that I want to enable and disable using code. I have
tried this: ActiveSheet.Shapes("PrintJour").Enabled = True Without any luck. Can anyone guide me? The Doctor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enableling a shape
Dim bEnable As Boolean
bEnable = False ' true to enable ActiveSheet.OLEObjects("CommandButton1").Object.En abled = bEnable Regards, Peter T "Dr. Schwartz" wrote in message ... I have a command button that I want to enable and disable using code. I have tried this: ActiveSheet.Shapes("PrintJour").Enabled = True Without any luck. Can anyone guide me? The Doctor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enableling a shape
Another one:
Worksheets("sheet1").CommandButton1.Enabled = False Dr. Schwartz wrote: I have a command button that I want to enable and disable using code. I have tried this: ActiveSheet.Shapes("PrintJour").Enabled = True Without any luck. Can anyone guide me? The Doctor -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enableling a shape
I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the boxes and lock them. If I look at the property sheet of the text box, I see it has an enabled property, but if I try to set it to enabled = false with VBA code, I get the error message that the object doesn't support the method. I added a new textbox "TextBox7" just for testing purposes. It appears that locking isn't working right either. Maybe it's something else I am doing... '****StartCode: Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Name = "TextBox7" Then shp.Locked = True ActiveSheet.Shapes("TextBox7").Enabled = False End If Next shp Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select Selection.Locked = True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=strPassword '****EndCode Please help? Thanks. Keith "Dave Peterson" wrote: Another one: Worksheets("sheet1").CommandButton1.Enabled = False Dr. Schwartz wrote: I have a command button that I want to enable and disable using code. I have tried this: ActiveSheet.Shapes("PrintJour").Enabled = True Without any luck. Can anyone guide me? The Doctor -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enableling a shape
ps. Maybe the worksheet is protected before your code hits the .locked line.
If that doesn't help, how is it failing? Keith Broaddrick wrote: I have a worksheet with some buttons and text boxes and combo boxes, all from the control toolbox. I am looping through them and wanting to disable the boxes and lock them. If I look at the property sheet of the text box, I see it has an enabled property, but if I try to set it to enabled = false with VBA code, I get the error message that the object doesn't support the method. I added a new textbox "TextBox7" just for testing purposes. It appears that locking isn't working right either. Maybe it's something else I am doing... '****StartCode: Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Name = "TextBox7" Then shp.Locked = True ActiveSheet.Shapes("TextBox7").Enabled = False End If Next shp Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select Selection.Locked = True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=strPassword '****EndCode Please help? Thanks. Keith "Dave Peterson" wrote: Another one: Worksheets("sheet1").CommandButton1.Enabled = False Dr. Schwartz wrote: I have a command button that I want to enable and disable using code. I have tried this: ActiveSheet.Shapes("PrintJour").Enabled = True Without any luck. Can anyone guide me? The Doctor -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enableling a shape
Just use the control directly:
ActiveSheet.textbox7.Enabled = False Or ActiveSheet.OLEObjects("textbox7").Object.Enabled = False Keith Broaddrick wrote: I have a worksheet with some buttons and text boxes and combo boxes, all from the control toolbox. I am looping through them and wanting to disable the boxes and lock them. If I look at the property sheet of the text box, I see it has an enabled property, but if I try to set it to enabled = false with VBA code, I get the error message that the object doesn't support the method. I added a new textbox "TextBox7" just for testing purposes. It appears that locking isn't working right either. Maybe it's something else I am doing... '****StartCode: Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Name = "TextBox7" Then shp.Locked = True ActiveSheet.Shapes("TextBox7").Enabled = False End If Next shp Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select Selection.Locked = True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=strPassword '****EndCode Please help? Thanks. Keith "Dave Peterson" wrote: Another one: Worksheets("sheet1").CommandButton1.Enabled = False Dr. Schwartz wrote: I have a command button that I want to enable and disable using code. I have tried this: ActiveSheet.Shapes("PrintJour").Enabled = True Without any luck. Can anyone guide me? The Doctor -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enableling a shape
Thanks Dave.
Ok, I was able to use your first suggestion and it works. I can even just use it like this: TextBox7.enabled = False and that works too. Now I just have to figure out how to substitute the variable for each textbox so I can apply this to all of the other textboxes and combo boxes on the sheet, and not just "TextBox7". I've tried these to no avail: ActiveSheet.shp.Enabled = False shp.enabled = False ActiveSheet.Shapes(shp).enabled = False I've tried setting the shp.name to a string variable and using this: ActiveSheet.Shapes(strShpName).Enabled = False strShpName.Enabled = False I can't seem to get it right. What's the proper syntax to get the variable 'shp' to react the same as using the control's explicit name? Also, I have noticed that this when I execute this line: activesheet.textbox7.locked = true without disabling it, I can still enter text in the box. What I ultimately am trying to do is loop through all the editable controls on the sheet and disable and lock them, but keep the text 'undimmed' so the box looks normal, but can't receive the focus. I can do this type of thing no problem in Access VBA, but I am finding the Excel VBA is a whole 'nother animal... Thanks. Keith "Dave Peterson" wrote: Just use the control directly: ActiveSheet.textbox7.Enabled = False Or ActiveSheet.OLEObjects("textbox7").Object.Enabled = False Keith Broaddrick wrote: I have a worksheet with some buttons and text boxes and combo boxes, all from the control toolbox. I am looping through them and wanting to disable the boxes and lock them. If I look at the property sheet of the text box, I see it has an enabled property, but if I try to set it to enabled = false with VBA code, I get the error message that the object doesn't support the method. I added a new textbox "TextBox7" just for testing purposes. It appears that locking isn't working right either. Maybe it's something else I am doing... '****StartCode: Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Name = "TextBox7" Then shp.Locked = True ActiveSheet.Shapes("TextBox7").Enabled = False End If Next shp Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select Selection.Locked = True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=strPassword '****EndCode Please help? Thanks. Keith "Dave Peterson" wrote: Another one: Worksheets("sheet1").CommandButton1.Enabled = False Dr. Schwartz wrote: I have a command button that I want to enable and disable using code. I have tried this: ActiveSheet.Shapes("PrintJour").Enabled = True Without any luck. Can anyone guide me? The Doctor -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enableling a shape
If you know the names of the textboxes (are they all textboxes????), then you
may be able to do something like this: Dim iCtr as long for ictr = 1 to 7 me.OLEObjects("textbox" & ictr).Object.Enabled = False next ictr This assumes that the code is in the worksheet module (the Me keyword refers to the worksheet that owns the code) and your textboxes are named textbox1, textbox2, ..., textbox7 Or just 7 different lines: me.textbox1.enabled = false me.textbox2.enabled = false me.textbox3.enabled = false .... me.textbox7.enabled = false If you don't know how many textboxes there are--or they aren't named nicely: Dim OLEObj As OLEObject For Each OLEObj In ActiveSheet.OLEObjects If TypeOf OLEObj.Object Is MSForms.TextBox Then OLEObj.enabled = false End If Next OLEObj Keith Broaddrick wrote: Thanks Dave. Ok, I was able to use your first suggestion and it works. I can even just use it like this: TextBox7.enabled = False and that works too. Now I just have to figure out how to substitute the variable for each textbox so I can apply this to all of the other textboxes and combo boxes on the sheet, and not just "TextBox7". I've tried these to no avail: ActiveSheet.shp.Enabled = False shp.enabled = False ActiveSheet.Shapes(shp).enabled = False I've tried setting the shp.name to a string variable and using this: ActiveSheet.Shapes(strShpName).Enabled = False strShpName.Enabled = False I can't seem to get it right. What's the proper syntax to get the variable 'shp' to react the same as using the control's explicit name? Also, I have noticed that this when I execute this line: activesheet.textbox7.locked = true without disabling it, I can still enter text in the box. What I ultimately am trying to do is loop through all the editable controls on the sheet and disable and lock them, but keep the text 'undimmed' so the box looks normal, but can't receive the focus. I can do this type of thing no problem in Access VBA, but I am finding the Excel VBA is a whole 'nother animal... Thanks. Keith "Dave Peterson" wrote: Just use the control directly: ActiveSheet.textbox7.Enabled = False Or ActiveSheet.OLEObjects("textbox7").Object.Enabled = False Keith Broaddrick wrote: I have a worksheet with some buttons and text boxes and combo boxes, all from the control toolbox. I am looping through them and wanting to disable the boxes and lock them. If I look at the property sheet of the text box, I see it has an enabled property, but if I try to set it to enabled = false with VBA code, I get the error message that the object doesn't support the method. I added a new textbox "TextBox7" just for testing purposes. It appears that locking isn't working right either. Maybe it's something else I am doing... '****StartCode: Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Name = "TextBox7" Then shp.Locked = True ActiveSheet.Shapes("TextBox7").Enabled = False End If Next shp Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select Selection.Locked = True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=strPassword '****EndCode Please help? Thanks. Keith "Dave Peterson" wrote: Another one: Worksheets("sheet1").CommandButton1.Enabled = False Dr. Schwartz wrote: I have a command button that I want to enable and disable using code. I have tried this: ActiveSheet.Shapes("PrintJour").Enabled = True Without any luck. Can anyone guide me? The Doctor -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Evaluating if a Shape is a line or a shape | Excel Programming | |||
my curser changed from arrow shape to a cross shape???? | New Users to Excel | |||
Does Shape Exist? | Excel Programming | |||
Deleting a shape and the cell contents the shape is in. | Excel Programming | |||
Deleting a shape and the cell contents the shape is in. | Excel Programming |