Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use of a variable......
I use excel 2003 and have a sheet that has 258 small rectagular boxes on it. I use the fill color option to turn the boxes black if I want to show that I selected it and make it stand out. I was trying to create a macro that would take all 258 rectagle boxes and change their fill color to "no fill" to sort of 'clear' the sheet when needed. Problem is if I record the macro and start selecting all 258 then select the no fill option, the macro errors out on playback because it says it is to large. The boxes are named Rectangle 232, Rectangle 233, Rectangle 234, etc from 1 to 258. So I thought about using some sort of FOR X = 1 to 258 type routine that would clear out the boxes and the lines of code would then be very short but I can not figure out the correct syntex to use. This is what the code looks like if I simply select record a macro, then select one of the rectangles and then select 'no fill' and end the macro: Sub sample_clear() ' ActiveSheet.Shapes("Rectangle 232").Select Selection.ShapeRange.Fill.Visible = msoFalse End Sub I was thinking something like: For X = 1 To 258 ActiveSheet.Shapes("Rectangle (X)").Select Selection.ShapeRange.Fill.Visible = msoFalse Next X End Sub would work, but I can not seem to get the syntex correct on how to write in the variable. Any help would be great if I have not confused everyone :). Thanks, Larry -- ltyson ------------------------------------------------------------------------ ltyson's Profile: http://www.excelforum.com/member.php...fo&userid=2768 View this thread: http://www.excelforum.com/showthread...hreadid=391498 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
use of a variable......
Hi L,
Providing there are no other colored rectangles on the active sheet, try: Sub Tester ActiveSheet.Rectangles.ShapeRange.Fill.ForeColor.S chemeColor = 1 End Sub To return the rctangles to black, change 1 to 0. --- Regards, Norman "ltyson" wrote in message ... I use excel 2003 and have a sheet that has 258 small rectagular boxes on it. I use the fill color option to turn the boxes black if I want to show that I selected it and make it stand out. I was trying to create a macro that would take all 258 rectagle boxes and change their fill color to "no fill" to sort of 'clear' the sheet when needed. Problem is if I record the macro and start selecting all 258 then select the no fill option, the macro errors out on playback because it says it is to large. The boxes are named Rectangle 232, Rectangle 233, Rectangle 234, etc from 1 to 258. So I thought about using some sort of FOR X = 1 to 258 type routine that would clear out the boxes and the lines of code would then be very short but I can not figure out the correct syntex to use. This is what the code looks like if I simply select record a macro, then select one of the rectangles and then select 'no fill' and end the macro: Sub sample_clear() ' ActiveSheet.Shapes("Rectangle 232").Select Selection.ShapeRange.Fill.Visible = msoFalse End Sub I was thinking something like: For X = 1 To 258 ActiveSheet.Shapes("Rectangle (X)").Select Selection.ShapeRange.Fill.Visible = msoFalse Next X End Sub would work, but I can not seem to get the syntex correct on how to write in the variable. Any help would be great if I have not confused everyone :). Thanks, Larry -- ltyson ------------------------------------------------------------------------ ltyson's Profile: http://www.excelforum.com/member.php...fo&userid=2768 View this thread: http://www.excelforum.com/showthread...hreadid=391498 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
use of a variable......
Hi L,
For the looping solution, try: Sub Tester() Dim i As Long For i = 1 To 258 ActiveSheet.Rectangles("Rectangle " & i). _ ShapeRange.Fill.ForeColor.SchemeColor = 1 Next i End Sub --- Regards, Norman "ltyson" wrote in message ... I use excel 2003 and have a sheet that has 258 small rectagular boxes on it. I use the fill color option to turn the boxes black if I want to show that I selected it and make it stand out. I was trying to create a macro that would take all 258 rectagle boxes and change their fill color to "no fill" to sort of 'clear' the sheet when needed. Problem is if I record the macro and start selecting all 258 then select the no fill option, the macro errors out on playback because it says it is to large. The boxes are named Rectangle 232, Rectangle 233, Rectangle 234, etc from 1 to 258. So I thought about using some sort of FOR X = 1 to 258 type routine that would clear out the boxes and the lines of code would then be very short but I can not figure out the correct syntex to use. This is what the code looks like if I simply select record a macro, then select one of the rectangles and then select 'no fill' and end the macro: Sub sample_clear() ' ActiveSheet.Shapes("Rectangle 232").Select Selection.ShapeRange.Fill.Visible = msoFalse End Sub I was thinking something like: For X = 1 To 258 ActiveSheet.Shapes("Rectangle (X)").Select Selection.ShapeRange.Fill.Visible = msoFalse Next X End Sub would work, but I can not seem to get the syntex correct on how to write in the variable. Any help would be great if I have not confused everyone :). Thanks, Larry -- ltyson ------------------------------------------------------------------------ ltyson's Profile: http://www.excelforum.com/member.php...fo&userid=2768 View this thread: http://www.excelforum.com/showthread...hreadid=391498 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
use of a variable......
Norman, Both of your suggestions worked perfectly for what I was trying to do. Thank you for the help, Larry -- ltyson ------------------------------------------------------------------------ ltyson's Profile: http://www.excelforum.com/member.php...fo&userid=2768 View this thread: http://www.excelforum.com/showthread...hreadid=391498 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variable height variable width stacked bar charts | Charts and Charting in Excel | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |