Clickable button colour change through VBA

General Computing Coding and Design
1

I need to revise & simplify a work project and I need to see is it possible for a clickable button to change colour dynamically when conditions on a worksheet are met, i.e. this cell and that cell both have a value in them, so button foreground colour changes.

I tried this, but it doesn’t work, it’s not dynamic and needs a button to be clicked:

Sub AlertUser()
’
’ Macro3 Macro
’ Macro recorded 20/03/2008 by Andy Young
’
Sheets(“2”).Select
Range(“A1”).Select
If value > 0 Then
Sheets(“1”).Select
ActiveSheet.Shapes(“Rectangle 1”).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 1
Selection.ShapeRange.Fill.Transparency = 0#
Else
Sheets(“1”).Select
ActiveSheet.Shapes(“Rectangle 1”).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 6
Selection.ShapeRange.Fill.Transparency = 0#
End If
End Sub

Any ideas?

2

I don’t know if this will help you persue the right sort or googling… but my Excel VBA book mentions something about Setting Control Properties Interactively in relation to dialog boxes & buttons and mentions something called a ‘Cell Link’ belonging to ListBox. DropDown, ScrollBar, Spinner, Checkbox, OptionButton. Purpose = Links a workcell to the controls value properties.

No info on invoking it though :frowning:

3

Doesn’t look like something I’ve come across in my searches, so might be worth a :google: Cheers Paul

4

Put your code into the Worksheet_Change event for sheet “2”; it will run every time a cell is changed.

5

Thanks for that Kevin. I’ll try it when I’m back in work on Tuesday :cheers:

6

I settled for a simpler solution by not using a clickable button, but using a hyperlink in a cell. This will reduce the need for extra buttons and macros and reduce the file size somewhat.

The only new issue resulting from this is the hyperlink when clicked stays in a “visited link” state and won’t revert back. I know how to reset this using style sheets in a web page, but how do you do it in Excel please?

/edit sorted it. Formatted cell with desired colour :doh: The new issue is how do you stop a mouseover text display on the hyperlinks?