The sub is being called, I run it manually by stepping through the code so I
know it's being run. I just replace the earlier sub with this:
Private Sub cmdMarketRegion_Click()
If Me.cmdMarketRegion.Caption = "Market" Then
Me.cmdMarketRegion.Caption = "Region"
Me.Range("D7,D23,D39") = "Market"
Me.Cells.Select
With Selection
.Replace What:="=$D$7", Replacement:="<""", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Me.Range("A2").Select

ElseIf Me.cmdMarketRegion.Caption = "Region" Then
Me.cmdMarketRegion.Caption = "Market"
Me.Range("D7,D23,D39") = "Region"
Me.Cells.Select
With Selection
.Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)",
Replacement:="--('InSite Milestones'!$A$2:$A$9245<"")", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, ReplaceFormat:=False
End With
Me.Range("A2").Select
End If
Me.Calculate
End Sub

Still doesn't work.

"OssieMac" wrote:

Hi Ayo,

Firstly put a MsgBox in the sub immediately after the sub name to ensure it
is being called.

Next (and you should do this anyway) include all of the arguments/parameters
for the Replace. Excel remembers the last used arguments/parameters for both
Find and Replace irrespective of whether they are used in the interactive
mode or in code. The 'remembered' arguments/parameters are then used as the
defaults if they are not reset in the code. Easiest method of getting them
correct is to record the Replace code.

You cannot be sure what a user has been doing with Find and Replace prior to
running the code so it is essential to reset the arguments/parameters.

--
Regards,

OssieMac


"Ayo" wrote:

When I run this click macro from inside a worksheet, nothing happens. The
changes I am trying to make are not done but the code works fine when I run
it manually.
Any ideas what is going wrong?

Private Sub cmdMarketRegion_Click()
If Me.cmdMarketRegion.Caption = "Market" Then
Me.cmdMarketRegion.Caption = "Region"
Me.Range("D7,D23,D39") = "Market"
Me.Cells.Select
With Selection
.Replace What:="=$D$7", Replacement:="<"""
End With
Me.Range("A2").Select
ElseIf Me.cmdMarketRegion.Caption = "Region" Then
Me.cmdMarketRegion.Caption = "Market"
Me.Range("D7,D23,D39") = "Region"
Me.Cells.Select
With Selection
.Replace What:="--('InSite Milestones'!$A$2:$A$9245=$E$4)",
Replacement:="--('InSite
Milestones'!$A$2:$A$9245<"")"
End With
Me.Range("A2").Select
End If

Me.Calculate
End Sub