How do I Create a Macro to run VB Code in Excel?

[Gator]

I have a Private Sub named Worksheet_SelectionChange(ByVal Target As Range)
How do I create a macro to run this code. I am trying to assign VB Code to
run when I click an icon that I made. I get stuck when I have to assign a
macro to the icon. Do I just use the sub name above? Do I create a macro to
run the sub and then assign the icon to the macro?
HELP

 

[Gary''s Student]

We can do this in two steps:

1. transfer the logic from the event macro to a public macro
2. assign the public macro to an icon or shape

Here is an example:

we start with:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox ("Hello World")
End Sub

The first thing to to create a public macro in a standard module:

Public Sub gator()
MsgBox ("hello World")
End Sub

and the old event macro gets modified to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
call gator
End Sub

At this point any routine can call gator. Insert any picture in the
worksheet; right-click the picture and assign it the sub gator

 

[Gator]

Here is the code I have. How do I tie three sub procedures into one macro?

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
AddRowBorders Target.EntireRow
With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
End With
AddColumnBorders Target.EntireColumn

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub

Private Sub AddRowBorders(pRow As Range)
With pRow
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
End Sub

Private Sub AddColumnBorders(pColumn As Range)
With pColumn
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
End Sub

 

[Don Guillett]

..Borders.LineStyle = xlContinuous
or
..BorderAround ColorIndex:=5, Weight:=xlThin

Might be best to tell us exactly what you want to happen when you select a
cell. And, what you want when you select a different cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
'AddRowBorders Target.EntireRow
With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
End With

'AddColumnBorders Target.EntireColumn
..Borders.LineStyle = xlContinuous

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub

 

[Bob Phillips]

Assuming that the target cell is the activecell when the icon is clicked,
then in a standard module create

'----------------------------------------------------------------
Public Sub SetHighlight(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
AddRowBorders Target.EntireRow
With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
End With
AddColumnBorders Target.EntireColumn

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub

Private Sub AddRowBorders(pRow As Range)
With pRow
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
End Sub

Private Sub AddColumnBorders(pColumn As Range)
With pColumn
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
End Sub


then add a new macro in a standrad code module

Public Sub Icon_Click()
Call SetHighlight(Activecell)
End Sub

and finally change the worksheet change event to


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call SetHighlight(Target)
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gator]

I want to be able to turn on and off the ability to click in a cell and have
the respective column and row highlight. And/or hover mouse over cell and
have that ability. I have code that will do this, that is click in a cell
and have the row and column highlight. But it only works in one particular
worksheet. I want this ability for all workbooks and I want it to be in the
form of an icon or menu check item to turn on/off the highlight feature.

 

[Gator]

Bob
what code can I use that will disable the macro and clear the formatting?

 

[Bob Phillips]

'----------------------------------------------------------------
Public Sub ClearHighlight(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)