Worksheet_Change

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
372
Office Version
  1. 365
Platform
  1. Windows
Please help, I have entered the below code which produces the second table but don't now how to do the same for more than one table in the same sheet.

I hope you can help.
:nya:
<TABLE style="WIDTH: 610pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=812 border=0 x:str><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=3 width=96><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" span=2 width=118><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=3 width=96><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl32 style="BORDER-RIGHT: black 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; WIDTH: 394pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" width=524 colSpan=5 height=19>CUBIC METRES CALCULATOR USING INCHES</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" width=96></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" width=96></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" width=96></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl26 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" height=19>Width (In)</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver">Length (In)</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver">Height (In)</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver">No. of Packages</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver">Cubic Metres</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffff99" height=19 x:num>23.62</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>23.62</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>23.62</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>1</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" x:num="0.2159438448670597" x:fmla="=E4">0.22</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" height=18></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" align=middle x:num="13177.701928" x:fmla="=A3*B3*C3">##################</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" align=right x:num="0.2159438448670597" x:fmla="=D4/61023.7440947322">0.21594384</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" height=18></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl32 style="BORDER-RIGHT: black 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" colSpan=8 height=19>CONVERTING LENGTH</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" width=96 height=19>Millimeters</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Centimeters</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Inches</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=118>Feet</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=118>Yards</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Meter</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Kilometers</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Miles</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffff99" height=19 x:num>304.80000</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>30.48000</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>12.00000</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>1.00000</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="0.33333000000000002">0.33333</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="0.30480000000000002">0.30480</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="2.9999999999999997E-4">0.00030</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="1.9000000000000001E-4">0.00019</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" height=18></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR></TBODY></TABLE>

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D26:K26")) Is Nothing Then
Application.EnableEvents = False

Select Case Target.Address

Case "$D$26"

Range("E26") = Range("D26") / 10
Range("F26") = Range("D26") / 25.4
Range("G26") = Range("D26") / 304.8
Range("H26") = Range("D26") / 914.4
Range("I26") = Range("D26") / 1000
Range("G26") = Range("D26") / 1000000
Range("K26") = Range("D26") / 1609344

Case "$E$26"

Range("D26") = Range("E26") * 10
Range("F26") = Range("E26") * 0.3937
Range("G26") = Range("E26") * 0.03281
Range("H26") = Range("E26") * 0.01094
Range("I26") = Range("E26") * 0.01
Range("J26") = Range("E26") * 0.00001
Range("K26") = Range("E26") * 0.00001

Case "$F$26"

Range("D26") = Range("F26") * 25.4
Range("E26") = Range("F26") * 2.54
Range("G26") = Range("F26") * 0.08333
Range("H26") = Range("F26") * 0.02778
Range("I26") = Range("F26") * 0.0254
Range("J26") = Range("F26") * 0.00003
Range("K26") = Range("F26") * 0.00002

Case "$G$26"

Range("D26") = Range("G26") * 304.8
Range("E26") = Range("G26") * 30.48
Range("F26") = Range("G26") * 12
Range("H26") = Range("G26") * 0.33333
Range("I26") = Range("G26") * 0.3048
Range("J26") = Range("G26") * 0.0003
Range("K26") = Range("G26") * 0.00019
Case "$H$26"

Range("D26") = Range("H26") * 914.4
Range("E26") = Range("H26") * 91.44
Range("F26") = Range("H26") * 36
Range("G26") = Range("H26") * 3
Range("I26") = Range("H26") * 0.9144
Range("G26") = Range("H26") * 0.00091
Range("K26") = Range("H26") * 0.00057
Case "$I$26"

Range("D26") = Range("I26") * 1000
Range("E26") = Range("I26") * 100
Range("F26") = Range("I26") * 39.3701
Range("G26") = Range("I26") * 3.28084
Range("H26") = Range("I26") * 1.09361
Range("J26") = Range("I26") * 0.001
Range("K26") = Range("I26") * 0.00062
Case "$J$26"

Range("D26") = Range("J26") * 1000000
Range("E26") = Range("J26") * 100000
Range("F26") = Range("J26") * 39370.1
Range("G26") = Range("J26") * 3280.84
Range("H26") = Range("J26") * 1093.61
Range("I26") = Range("J26") * 1000
Range("K26") = Range("J26") * 0.62137
Case "$K$26"

Range("D26") = Range("K26") * 1609344
Range("E26") = Range("K26") * 160934
Range("F26") = Range("K26") * 63360
Range("G26") = Range("K26") * 5280
Range("H26") = Range("K26") * 1760
Range("I26") = Range("K26") * 1609
Range("J26") = Range("K26") * 1.60934

End Select

Application.EnableEvents = True
End If
End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
That worked with the first table. Do i just keep entering the, Case "$D$26" formulars to do the second table?
 
Upvote 0
To handle the possibility that Target consists of more than one cell including one or more of the trigger cells, suggest that this :-

Code:
If Target.Count > 1 Then Exit Sub

be changed to this :-

Code:
Dim rng As Range
Set rng = Intersect(Target, Range("D26,F26"))
If Not rng Is Nothing And Target.Count > 1 Then
    MsgBox "Only one cell at a time can be changed, if changes are made to D26 and F26."
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
    Exit Sub
End If

Otherwise, changes could be made to D26 and F26 without triggering the code.
 
Upvote 0
Boiler, his range is not D16,F26
It is D26:K26 and D32:K32 and he only wants to change one cell at a time. I have the advantage of having seen his WorkBook.

That said, your code is quite clever. I will remember it.

lenze
 
Upvote 0
Without changing the If Target.Count text it appears to be working by just following on with the case statement and then formulars. At no point will the tables need to interact so i guess this is fine. As i'm just learning VB in excel i will test what your code does. Many thanks Lenze / Boller.

Mark
 
Upvote 0
"he only wants to change one cell at a time"

This is why I made the suggestion.

The code I suggested prevents changes to the trigger cells unless the change is one cell at a time.

Without this code revision, changes could be made to the trigger cells without the code being run (by changing multiple cells at one time - either deliberately or inadvertently).

The following may or may not be more appropriate than my previous suggestion (revision highlighted in red) :-

Code:
Dim rng As Range
Set rng = Intersect(Target, Range([I][COLOR=black]whatever[/COLOR][/I]))
[COLOR=red]If Not rng Is Nothing And Rng.Count > 1 Then
[/COLOR]    MsgBox "Only one cell at a time can be changed, if changes are made to D26 and F26."
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
    Exit Sub
End If
 
Upvote 0
Boiler:
I thought about your suggestion and it does make sense. Because I have seen the sheet, I did not feel that changing 2 cells would be a likely occurance(But you never know). As Mike says, he's new to VBA so I tried to keep it simple. That's why I defined the range as I did. I usually try to post to the OP's persumed level as we can always enhance.

As I said, your suggestion certainly has merit.

lenze
 
Upvote 0

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top