Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto run macro | Excel Worksheet Functions | |||
Auto run macro | Excel Discussion (Misc queries) | |||
Sub Macro vrs Function Macro Auto Start | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Auto-run macro after auto-query refresh (Excel2000,sr1) | Excel Programming |