PDA

View Full Version : [SOLVED] Problem with Macro



Pawel
07-21-2016, 09:54 AM
Hi guys,
I'm experiencing some trouble with Mcro code. I'm trying to write macro which will be run only on shapes ( checkboxes or option buttons) that exist within the range of selected cell or cells.
for example:
If I select cell that " contains" a shape, then I run macro - it will only change the colour of of shape range ( that field around the shape when we select it)

as for now I have sth like this: (but it's not working:banghead:)

Public Sub check()


Dim RngClrBut As Range
Dim ObClrBut As Shape




If Selection.Count >= 1 Then
Set RngClrBut = Selection.Cells.SpecialCells(xlVisible)
On Error Resume Next
For Each ob In ActiveSheet.Shapes
If Not Application.Intersect(ob.TopLeftCell, rg) Is Nothing Then

ActiveSheet.CheckBoxes(ObClrBut.Name).Interior.ColorIndex = 10
ActiveSheet.OptionButtons(ObClrBut.Name).Interior.ColorIndex = 10
End If
End If

End Sub

Any help will be much appreciated.

p45cal
07-21-2016, 10:11 AM
ry this:
Public Sub blah()
Dim RngClrBut As Range, Ob
If Selection.Count > 0 Then
Set RngClrBut = Selection.SpecialCells(xlVisible)
For Each Ob In ActiveSheet.CheckBoxes
If Not Application.Intersect(Ob.TopLeftCell, RngClrBut) Is Nothing Then Ob.Interior.ColorIndex = 10
Next Ob
For Each Ob In ActiveSheet.OptionButtons
If Not Application.Intersect(Ob.TopLeftCell, RngClrBut) Is Nothing Then Ob.Interior.ColorIndex = 10
Next Ob
End If
End Sub

Pawel
07-21-2016, 10:17 AM
Works Perfect!

Thank you so much!