Macro Box Help

P

Peter

Hello all, I have created a box with a macro assigned to it that will
highlight the cells that have changed from one tab to the other. When I click
on this box the macro runs but I would also like to be able to click it off.
Could someone please help me with this? I have copied the code below that has
been used so far in case that helps. Thanks.

Sub auditt()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Original")
Set sh2 = Sheets("Final")
For Each r In sh1.UsedRange
v1 = r.Value
rr = r.Row
cc = r.Column
v2 = sh2.Cells(rr, cc).Value
If v1 <> v2 Then
sh2.Cells(rr, cc).Interior.ColorIndex = 36
End If
Next

End Sub
 
O

OssieMac

Hi Peter,

Warning: Make a backup copy of your workbook before installing and running
the macro below in case it does not work as you anticipated.

I assume that when you refer to a macro box you mean a button to run a
macro. I also assume from the macro name that this is a Forms Button.

If my assumptions are correct then the way to achieve what you want is to
rename the button caption each time the macro is run. You can then use the
button caption in conjunction with an 'If' statement to determine whether to
apply color or remove color.

The following should achieve what you want. However, you need to identify
the button code name and edit the name in the macro if required. To do this:-

Turn on macro recording.
Right click the button to select it.
Click back on any other cell to deselect the button.
Turn off macro recording.
Find the module where the recording took place and you will see the button
code name in a line of code that looks like this:-
ActiveSheet.Shapes("Button 1").Select
"Button 1" is the code name. If yours is Button 1 then the macro will work
as is but if not then replace Button 1 with your button name everywhere it
appears in the code.

You can remove the recorded macro and module.

You will also need to edit the existing caption on the button and in the
macro so that they match for the initial running of the code.

As a note of interest about the code, the only way I can return or change
the button caption in the code is to use code to select the button first.

Feel free to get back to me if you have any problems.

Sub auditt()

Dim sh1 As Worksheet, sh2 As Worksheet
Dim strCaption As String
Dim cellChanged As Boolean

Set sh1 = Sheets("Original")
Set sh2 = Sheets("Final")

'Select and assign button caption to variable
ActiveSheet.Shapes("Button 1").Select
'Assign button caption to a variable
strCaption = Selection.Characters.Text
'Deactivate the button
Range("A1").Select

If strCaption = "Color the changed cells" Then
cellChanged = False
For Each r In sh1.UsedRange
v1 = r.Value
rr = r.Row
cc = r.Column
v2 = sh2.Cells(rr, cc).Value
If v1 <> v2 Then
sh2.Cells(rr, cc).Interior.ColorIndex = 36
cellChanged = True
End If
Next
If cellChanged = True Then
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Remove color from cells"
Range("A1").Select
End If
Else
sh2.UsedRange.Interior.ColorIndex = xlColorIndexNone
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Color the changed cells"
Range("A1").Select
End If

End Sub

Regards,

OssieMac
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Boxes 0
macro button on/off 1
Macro to copy row based on value into new sheet of same name 9
Move rows based on cell info 4
Run and edit a Macro 1
Help Please 7
Macro Help 2
Shift data from row to row 3

Top