Events and values and Ranges
 

Hello,
I have a situation here. If worksheet 1 has the following data :

A
1 CATEGORY
2 Income
3 Expenses
4 Junk
5 Others
6 Whatever

Assume all the values (A2 to A6 ) are in a dynamic Name range. "CATEGORIES".
These values are used in Sheet2. When I click on say A5 and change "Others"
to "SAMPLE", I want to display a messagebox and prompt for automatic
updating on the worksheet which uses these categories(Sheet2). How can I do
this ? Any suggestions from MVPs ?

Also,when we click in a cell and change a certain value, how can we get a
reference to the old value ? There must be a way, since Excel can put the
data back using the undo (ctrl+z) option. Which object is used for this ?
Any suggestions ? I am new to excel vba coding. Patience is appreciated.

Thanks

Events and values and Ranges
 

Srikanth,

You can use an event: copy the code below, then right click on the sheet tab
of the sheet with the range named "Categories", then select "View Code" and
paste the code into the wondow that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal As String
Dim NewVal As String
Dim mySht As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("Categories")) Is Nothing Then
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
If MsgBox("Do you want to do a global replace?", vbYesNo) = vbYes Then
For Each mySht In ThisWorkbook.Worksheets
mySht.Cells.Replace OldVal, NewVal, xlWhole
Next mySht
End If
Application.EnableEvents = True
End If
End Sub



"Srikanth Veera" wrote in message
...
Hello,
I have a situation here. If worksheet 1 has the following data :

A
1 CATEGORY
2 Income
3 Expenses
4 Junk
5 Others
6 Whatever

Assume all the values (A2 to A6 ) are in a dynamic Name range.
"CATEGORIES".
These values are used in Sheet2. When I click on say A5 and change
"Others"
to "SAMPLE", I want to display a messagebox and prompt for automatic
updating on the worksheet which uses these categories(Sheet2). How can I
do
this ? Any suggestions from MVPs ?

Also,when we click in a cell and change a certain value, how can we get a
reference to the old value ? There must be a way, since Excel can put the
data back using the undo (ctrl+z) option. Which object is used for this ?
Any suggestions ? I am new to excel vba coding. Patience is appreciated.

Thanks

Events and values and Ranges
 

It sure works great Bernie. I wasn't even aware of the Intersect function.
Well, for someone who just started with VBA excel, not a surprise !!

When we click yes as the response to the message box, all the references of
the old value are updated to new value.
But when we select "NO" as the response to the message box, then the new
value just sits there in the edited cell. I tried using the following :

NOTE : Just took a segment from your code snippet.

If MsgBox("Do you want to do a global replace?", vbYesNo) = vbYes
Then
For Each mySht In ThisWorkbook.Worksheets
mySht.Cells.Replace OldVal, NewVal, xlWhole
Next mySht
Else
Application.Undo
End If

I get the error _Application not valid.

Also, what is the xlWhole variable ? Is it a reserved variable in VBA ?

How do I check for duplicate category ( when we enter an already existing
category ).

Thanks in advance




"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Srikanth,

You can use an event: copy the code below, then right click on the sheet
tab
of the sheet with the range named "Categories", then select "View Code"
and
paste the code into the wondow that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal As String
Dim NewVal As String
Dim mySht As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("Categories")) Is Nothing Then
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
If MsgBox("Do you want to do a global replace?", vbYesNo) = vbYes Then
For Each mySht In ThisWorkbook.Worksheets
mySht.Cells.Replace OldVal, NewVal, xlWhole
Next mySht
End If
Application.EnableEvents = True
End If
End Sub



"Srikanth Veera" wrote in message
...
Hello,
I have a situation here. If worksheet 1 has the following data :

A
1 CATEGORY
2 Income
3 Expenses
4 Junk
5 Others
6 Whatever

Assume all the values (A2 to A6 ) are in a dynamic Name range.
"CATEGORIES".
These values are used in Sheet2. When I click on say A5 and change
"Others"
to "SAMPLE", I want to display a messagebox and prompt for automatic
updating on the worksheet which uses these categories(Sheet2). How can I
do
this ? Any suggestions from MVPs ?

Also,when we click in a cell and change a certain value, how can we get
a
reference to the old value ? There must be a way, since Excel can put
the
data back using the undo (ctrl+z) option. Which object is used for this
?
Any suggestions ? I am new to excel vba coding. Patience is appreciated.

Thanks

Events and values and Ranges
 

Srikanth,

Try the version below.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal As String
Dim NewVal As String
Dim mySht As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("Categories")) Is Nothing Then
NewVal = Target.Value
If Application.CountIf(Range("Categories"), NewVal) 1 Then
MsgBox "You already have that value"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Else
Application.EnableEvents = False
If MsgBox("Do you want to do a global replace?", vbYesNo) = vbYes Then
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
For Each mySht In ThisWorkbook.Worksheets
mySht.Cells.Replace OldVal, NewVal, xlWhole
Next mySht
Else
Application.Undo
End If
Application.EnableEvents = True
End If
End If
End Sub




"Srikanth Veera" wrote in message
...
It sure works great Bernie. I wasn't even aware of the Intersect function.
Well, for someone who just started with VBA excel, not a surprise !!

When we click yes as the response to the message box, all the references
of
the old value are updated to new value.
But when we select "NO" as the response to the message box, then the new
value just sits there in the edited cell. I tried using the following :

NOTE : Just took a segment from your code snippet.

If MsgBox("Do you want to do a global replace?", vbYesNo) = vbYes
Then
For Each mySht In ThisWorkbook.Worksheets
mySht.Cells.Replace OldVal, NewVal, xlWhole
Next mySht
Else
Application.Undo
End If

I get the error _Application not valid.

Also, what is the xlWhole variable ? Is it a reserved variable in VBA ?

How do I check for duplicate category ( when we enter an already existing
category ).

Thanks in advance




"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Srikanth,

You can use an event: copy the code below, then right click on the sheet
tab
of the sheet with the range named "Categories", then select "View Code"
and
paste the code into the wondow that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal As String
Dim NewVal As String
Dim mySht As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("Categories")) Is Nothing Then
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
If MsgBox("Do you want to do a global replace?", vbYesNo) = vbYes Then
For Each mySht In ThisWorkbook.Worksheets
mySht.Cells.Replace OldVal, NewVal, xlWhole
Next mySht
End If
Application.EnableEvents = True
End If
End Sub



"Srikanth Veera" wrote in message
...
Hello,
I have a situation here. If worksheet 1 has the following data
:

A
1 CATEGORY
2 Income
3 Expenses
4 Junk
5 Others
6 Whatever

Assume all the values (A2 to A6 ) are in a dynamic Name range.
"CATEGORIES".
These values are used in Sheet2. When I click on say A5 and change
"Others"
to "SAMPLE", I want to display a messagebox and prompt for automatic
updating on the worksheet which uses these categories(Sheet2). How can
I
do
this ? Any suggestions from MVPs ?

Also,when we click in a cell and change a certain value, how can we
get
a
reference to the old value ? There must be a way, since Excel can put
the
data back using the undo (ctrl+z) option. Which object is used for
this
?
Any suggestions ? I am new to excel vba coding. Patience is
appreciated.

Thanks