Object with VBA Code

ASBeard

New Member
Joined
May 7, 2024
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I have created a simple object that I would like to use as a toggle button that when clicked, will color the object and then when clicked again it will return to no color. I have been trying the following code and keep getting different errors. Thanks in advance for your help!


VBA Code:
Sub Rectangle6_Click()
'
' Rectangle6_Click Macro
''
    If Rectangle6 = False Then
    ActiveSheet.Shapes.Range(Array("Rectangle 6", "Rectangle 19", "Rectangle 20", "Rectangle 21" _
        , "Rectangle 22", "Rectangle 23", "Rectangle 24")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End If
    If Rectangle6 = True Then
    ActiveSheet.Shapes.Range(Array("Rectangle 6", "Rectangle 19", "Rectangle 20", "Rectangle 21" _
        , "Rectangle 22", "Rectangle 23", "Rectangle 24")).Select
    Selection.ShapeRange.Fill.Visible = msoFalse
   
End With
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You should always state what the error message(s) is/are and show what line with a comment in the code. I might spot one, you might have 2 others that I don't see because of that.
Great that you used vba code tags. Not something I see much of the time, even when members have hundreds of posts. Your indentation needs a bit of work though, and doing that properly might reveal the error meaning to you. I imagine at least one error is something like "With without End With" or maybe "If without End If" ?
 
Upvote 1
You should always state what the error message(s) is/are and show what line with a comment in the code. I might spot one, you might have 2 others that I don't see because of that.
Great that you used vba code tags. Not something I see much of the time, even when members have hundreds of posts. Your indentation needs a bit of work though, and doing that properly might reveal the error meaning to you. I imagine at least one error is something like "With without End With" or maybe "If without End If" ?
Sorry for leaving out the error! Well, you nailed it on the error as I think I have seen every "End ..." error there is. The one I see with the current code is "End if without block if"
 
Upvote 0
So do you want me to re-write this and post that or would you like to fix your indentation and see if you can spot it? Your indentation is close, but not quite right. It might seem that I'm making too much of that, but I'm convinced it is why you can't/didn't see the problem. Based on your last post I'm not sure if you've figured it out or not.

In the meantime, someone might come along and give you your fish. I'm trying to teach how to fish here. :)
 
Upvote 0
So do you want me to re-write this and post that or would you like to fix your indentation and see if you can spot it? Your indentation is close, but not quite right. It might seem that I'm making too much of that, but I'm convinced it is why you can't/didn't see the problem. Based on your last post I'm not sure if you've figured it out or not.

In the meantime, someone might come along and give you your fish. I'm trying to teach how to fish here. :)
I would much prefer if you could teach me rather than just correcting the issue(s). I am sure you can tell that I am still learning VBA. Thank you for taking the time to help me!!
 
Upvote 0
OK, here is you code, properly indented as much as it can be, given that things are out of sync. See if you can spot the problem.
Clue - when nesting an If or With block inside of each other you must begin and end the inner one(s) before you end the outer one(s).
BTW, I would not begin a line in a line continuation format with a single character like that (the leading comma) so I changed it.
VBA Code:
Sub Rectangle6_Click()

    If Rectangle6 = False Then
        ActiveSheet.Shapes.Range(Array("Rectangle 6", "Rectangle 19", "Rectangle 20", "Rectangle 21," _
           "Rectangle 22", "Rectangle 23", "Rectangle 24")).Select
        With Selection.ShapeRange.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorText1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
    End If
    If Rectangle6 = True Then
        ActiveSheet.Shapes.Range(Array("Rectangle 6", "Rectangle 19", "Rectangle 20", "Rectangle 21," _
           "Rectangle 22", "Rectangle 23", "Rectangle 24")).Select
        Selection.ShapeRange.Fill.Visible = msoFalse
   
    End With
End Sub
 
Upvote 0
OK, here is you code, properly indented as much as it can be, given that things are out of sync. See if you can spot the problem.
Clue - when nesting an If or With block inside of each other you must begin and end the inner one(s) before you end the outer one(s).
BTW, I would not begin a line in a line continuation format with a single character like that (the leading comma) so I changed it.
VBA Code:
Sub Rectangle6_Click()

    If Rectangle6 = False Then
        ActiveSheet.Shapes.Range(Array("Rectangle 6", "Rectangle 19", "Rectangle 20", "Rectangle 21," _
           "Rectangle 22", "Rectangle 23", "Rectangle 24")).Select
        With Selection.ShapeRange.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorText1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
    End If
    If Rectangle6 = True Then
        ActiveSheet.Shapes.Range(Array("Rectangle 6", "Rectangle 19", "Rectangle 20", "Rectangle 21," _
           "Rectangle 22", "Rectangle 23", "Rectangle 24")).Select
        Selection.ShapeRange.Fill.Visible = msoFalse
  
    End With
End Sub

VBA Code:
Sub Rectangle6_Click()

    If Rectangle6 = False Then
        ActiveSheet.Shapes.Range(Array("Rectangle 6", "Rectangle 19", "Rectangle 20", "Rectangle 21" _
            , "Rectangle 22", "Rectangle 23", "Rectangle 24")).Select
        With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
        
    If Rectangle6 = True Then
        ActiveSheet.Shapes.Range(Array("Rectangle 6", "Rectangle 19", "Rectangle 20", "Rectangle 21" _
            , "Rectangle 22", "Rectangle 23", "Rectangle 24")).Select
        With Selection.ShapeRange.Fill.Visible = msoFalse
    End With
    End If
End If
End Sub

This works kind of. I am no longer getting my End If/With errors, but the button is not performing the task that I am trying to accomplish. It's filing the shapes as the first "If" function instructs however when I click again its only highlighting the shapes and not removing the fill. I do feel like I am close, any further hints or suggestions? Thank you for taking the time to teach me!!
 
Upvote 0
OK, here is you code, properly indented as much as it can be, given that things are out of sync. See if you can spot the problem.
Clue - when nesting an If or With block inside of each other you must begin and end the inner one(s) before you end the outer one(s).
BTW, I would not begin a line in a line continuation format with a single character like that (the leading comma) so I changed it.
VBA Code:
Sub Rectangle6_Click()

    If Rectangle6 = False Then
        ActiveSheet.Shapes.Range(Array("Rectangle 6", "Rectangle 19", "Rectangle 20", "Rectangle 21," _
           "Rectangle 22", "Rectangle 23", "Rectangle 24")).Select
        With Selection.ShapeRange.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorText1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
    End If
    If Rectangle6 = True Then
        ActiveSheet.Shapes.Range(Array("Rectangle 6", "Rectangle 19", "Rectangle 20", "Rectangle 21," _
           "Rectangle 22", "Rectangle 23", "Rectangle 24")).Select
        Selection.ShapeRange.Fill.Visible = msoFalse
  
    End With
End Sub
Hello Micron, just wanted to touch base and see if you were able to look at my last post of code that I sent. There is still something wrong and I am stuck. Thanks for your help!
 
Upvote 0
Step through your code (click on a line and repeatedly press F8) and see if it executes as you expect.
TBH I'm not too familiar with shape properties (your rectangle is a shape?) and don't know how the True/False part you have applies. As for not removing fill, I see no code that will do that. It looks to me as if you're trying to set the visible property, not alter fills like in the first IF.
With Selection.ShapeRange.Fill.Visible = msoFalse
End With
BTW, you usually don't need to Select in just about any case, in order to copy/alter/etc. and it's usually considered to be inefficient. If you use that and make something invisible then try to Select it, you will likely raise a run time error.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top