Auto Run a Macro
 

Hi, I'm hoping that someone will take a look at this for me....

I would like this macro to auto run whenever there is a change to the
contents of the cells within the given range. The range comprises the linked
cells from drop down boxes and are alphanumeric lables (if that makes a
difference)

The code which I have assembled from loads of posts on this site is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$AA$11:$AA$16" Then
Call ConfirmSelection
End If
End Sub

Sub ConfirmSelection()
Dim Msg As String
Select Case Range("AB16").Value = "5.5" And _
Range("AB15").Value = 4 And Range("AB15").Value <= 7.1
Case True
Msg = "OK"
Case Else
Select Case Range("AB16").Value = "6.7" And _
Range("AB15").Value = 4 And Range("AB15").Value <= 8.8
Case True
Msg = "OK"
Case Else
Select Case Range("AB16").Value = "6.8" And _
Range("AB15").Value = 4 And Range("AB15").Value <= 10.6
Case True
Msg = "OK"
Case Else
Select Case Range("AB16").Value = "8" And _
Range("AB15").Value = 7.8 And Range("AB15").Value <= 14.3
Case True
Msg = "OK"
Case Else
Msg = "Not OK, Please Re-select"
End Select
End Select
End Select
End Select
MsgBox "Indoor/Outdoor Unit Selection " & "" & Msg
End Sub


When I activate the code manually it works perfectly, but I just can't get
it work automatically.

Really hoping that someone can help.

Thanks

Anne

Auto Run a Macro
 

On Jul 30, 10:02 am, AMell wrote:
Hi, I'm hoping that someone will take a look at this for me....

I would like this macro to auto run whenever there is a change to the
contents of the cells within the given range. The range comprises the linked
cells from drop down boxes and are alphanumeric lables (if that makes a
difference)

The code which I have assembled from loads of posts on this site is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$AA$11:$AA$16" Then
Call ConfirmSelection
End If
End Sub

Sub ConfirmSelection()
Dim Msg As String
Select Case Range("AB16").Value = "5.5" And _
Range("AB15").Value = 4 And Range("AB15").Value <= 7.1
Case True
Msg = "OK"
Case Else
Select Case Range("AB16").Value = "6.7" And _
Range("AB15").Value = 4 And Range("AB15").Value <= 8.8
Case True
Msg = "OK"
Case Else
Select Case Range("AB16").Value = "6.8" And _
Range("AB15").Value = 4 And Range("AB15").Value <= 10.6
Case True
Msg = "OK"
Case Else
Select Case Range("AB16").Value = "8" And _
Range("AB15").Value = 7.8 And Range("AB15").Value <= 14.3
Case True
Msg = "OK"
Case Else
Msg = "Not OK, Please Re-select"
End Select
End Select
End Select
End Select
MsgBox "Indoor/Outdoor Unit Selection " & "" & Msg
End Sub

When I activate the code manually it works perfectly, but I just can't get
it work automatically.

Really hoping that someone can help.

Thanks

Anne

This code is similar to what you want, it actually capitalizes text
when it's entered on a given range, in this case A1:A10.
If you follow it through, you should be able to adjust for what you're
doing.
Hope it helps

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
End If
On Error GoTo ErrHandler:
If Not Application.Intersect(Me.Range("A1:A10"), Target) Is
Nothing Then
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
'Target.Value = StrConv(Target.Text, vbLowerCase)
Target.Value = StrConv(Target.Text, vbUpperCase)
'Target.Value = StrConv(Target.Text, vbProperCase)
Application.EnableEvents = True
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Auto Run a Macro
 

Thanks for your assistance with this. I'm going to try to adapt your code to
do what I need it to do.

Anne

"Limey" wrote:

On Jul 30, 10:02 am, AMell wrote:
Hi, I'm hoping that someone will take a look at this for me....

I would like this macro to auto run whenever there is a change to the
contents of the cells within the given range. The range comprises the linked
cells from drop down boxes and are alphanumeric lables (if that makes a
difference)

The code which I have assembled from loads of posts on this site is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$AA$11:$AA$16" Then
Call ConfirmSelection
End If
End Sub

Sub ConfirmSelection()
Dim Msg As String
Select Case Range("AB16").Value = "5.5" And _
Range("AB15").Value = 4 And Range("AB15").Value <= 7.1
Case True
Msg = "OK"
Case Else
Select Case Range("AB16").Value = "6.7" And _
Range("AB15").Value = 4 And Range("AB15").Value <= 8.8
Case True
Msg = "OK"
Case Else
Select Case Range("AB16").Value = "6.8" And _
Range("AB15").Value = 4 And Range("AB15").Value <= 10.6
Case True
Msg = "OK"
Case Else
Select Case Range("AB16").Value = "8" And _
Range("AB15").Value = 7.8 And Range("AB15").Value <= 14.3
Case True
Msg = "OK"
Case Else
Msg = "Not OK, Please Re-select"
End Select
End Select
End Select
End Select
MsgBox "Indoor/Outdoor Unit Selection " & "" & Msg
End Sub

When I activate the code manually it works perfectly, but I just can't get
it work automatically.

Really hoping that someone can help.

Thanks

Anne

This code is similar to what you want, it actually capitalizes text
when it's entered on a given range, in this case A1:A10.
If you follow it through, you should be able to adjust for what you're
doing.
Hope it helps

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
End If
On Error GoTo ErrHandler:
If Not Application.Intersect(Me.Range("A1:A10"), Target) Is
Nothing Then
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
'Target.Value = StrConv(Target.Text, vbLowerCase)
Target.Value = StrConv(Target.Text, vbUpperCase)
'Target.Value = StrConv(Target.Text, vbProperCase)
Application.EnableEvents = True
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub