Reputation: 33
I have a VBA code in Visio that will change the color of the shape if said shape is hyperlinked. Right now, I run it by using a simple command button. I want the macro to run when a change occurs in the worksheet. I know in excel if I wanted to do that I would simply place my code in the Workbook_Change sub, but in Visio I am lost.
Here is my current code:
Private Sub CommandButton1_Click()
Dim Sh As Visio.shape
Dim Link As Hyperlink
For Each Sh In Visio.ActivePage.Shapes '<~ loop through the shapes collection
For Each Link In Sh.Hyperlinks '<~ loop through the links collection
If Not Link.Address = "" Then '<~ check for a blank address
Sh.Cells("Fillbkgnd").Formula = "RGB(255,102,0)"
Sh.Cells("Fillforegnd").Formula = "RGB(255, 102, 0)" '<~ apply a color to the shape
End If
Next Link
Next Sh
End Sub
Any ideas?
Upvotes: 1
Views: 2914
Reputation: 33
@JonFournier I have revisted this and here is my code that lives in ThisDocument:
Public WithEvents Pg As Visio.Page
Private Sub Pg_CellChanged(ByVal Cell As IVCell)
Set Pg = Pages("Page-1")
If Cell.Section = visSectionHyperlink Then
Dim Sh As Visio.shape
Dim Link As Hyperlink
For Each Sh In Visio.ActivePage.Shapes '<~ loop through the shapes collection
For Each Link In Sh.Hyperlinks '<~ loop through the links collection
If Not Link.Address = "" Then '<~ check for a blank address
Sh.Cells("Fillbkgnd").Formula = "RGB(255,102,0)"
Sh.Cells("Fillforegnd").Formula = "RGB(255, 102, 0)" '<~ apply a color to the shape
End If
Next Link
Next Sh
Else
End If
End Sub
The code that I put inside works perfectly fine when paired with a command button, but I would like it to work when the shape is changed. What else should I add to the code to "instantiate the object" or to get it to run the way I need it to. I can't seem to get anything to work. Appreciate the help.
Again, I am sorry this is appearing as an answer, my work firewalls will not allow me to comment for some reason.
Upvotes: 1
Reputation: 4327
You can catch the CellChanged event on the Page object, and check if the changed cell is in the hyperlink shapesheet section.
In a class module:
Public Withevents Pg as Visio.Page
Private Sub Pg_CellChanged(ByVal Cell as IVCell)
If Cell.Section = visSectionHyperlink Then
' call your code here
End If
End Sub
You would need to instantiate the object and keep it alive to monitor your active page, but this is the general gist of something that would work for you, I think.
This would also live happily in ThisDocument, if you'd prefer that.
Upvotes: 0