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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - How do I Create a Macro to run VB Code in Excel? (https://www.excelbanter.com/excel-programming/403676-how-do-i-create-macro-run-vbulletin-code-excel.html)

gator

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

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
--
Gator

Gary''s Student

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

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

--
Gary''s Student - gsnu200763

"Gator" wrote:

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
--
Gator

gator

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

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

--
Gator

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200763

"Gator" wrote:

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
--
Gator

Don Guillett

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

Bob Phillips

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

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" wrote in message
...
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

--
Gator

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200763

"Gator" wrote:

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
--
Gator

gator

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

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

"Don Guillett" wrote:

..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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gator" wrote in message
...
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

--
Gator

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200763

"Gator" wrote:

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
--
Gator

gator

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

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

"Bob Phillips" wrote:

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" wrote in message
...
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

--
Gator

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200763

"Gator" wrote:

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
--
Gator

Bob Phillips

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

'----------------------------------------------------------------
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)

"Gator" wrote in message
...
Bob
what code can I use that will disable the macro and clear the formatting?
--
Gator

"Bob Phillips" wrote:

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" wrote in message
...
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

--
Gator

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200763

"Gator" wrote:

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
--
Gator

All times are GMT +1. The time now is 12:40 PM.