Worksheet_Change Function not working

E

eoinymc

Board Regular
Joined
Jan 29, 2009
Messages
203
Hi,

Just wondering if you can help me.

I have the following code in my "Dashboard" sheet.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)     
        If Range("BM48") = 1 Then
            MsgBox " Please enter either % OR Value.", vbExclamation, "Average Fare."
            Range("BG48:BL48").ClearContents
            Range("BG48").Select
        End If
        
        FormatGraph ("Front")
     
End Sub


Private Sub ComboBox1_Change()
    Set Dashb = Sheets("Dashboard")
    Set Ind = Sheets("Index")
    
    Dashb.Range("BP5").Value = ComboBox1.Value


    If (Dashb.Range("BP5") = "LF" Or Dashb.Range("BP5") = "S3/Rev") Then
        Dashb.Range(Dashb.Cells(10, 69), Dashb.Cells(15, 81)).NumberFormat = "0.0%"
        Dashb.ChartObjects("CompChart1").Activate
        ActiveChart.Axes(xlValue).Select
        Selection.TickLabels.NumberFormat = "0.0%"
    ElseIf (Dashb.Range("BP5") = "CASK" Or Dashb.Range("BP5") = "RASK" Or Dashb.Range("BP5") = "Cost per Seat" Or Dashb.Range("BP5") = "Rev per Seat" Or Dashb.Range("BP5") = "Avg Fare") Then
        Dashb.Range(Dashb.Cells(10, 69), Dashb.Cells(15, 81)).NumberFormat = "#,##0.00_ ;(#,##0.00)"
        Dashb.ChartObjects("CompChart1").Activate
        ActiveChart.Axes(xlValue).Select
        Selection.TickLabels.NumberFormat = "#,##0.00_ ;(#,##0.00)"
    Else
        Dashb.Range(Dashb.Cells(10, 69), Dashb.Cells(15, 81)).NumberFormat = "#,##0_ ;(#,##0)"
        Dashb.ChartObjects("CompChart1").Activate
        ActiveChart.Axes(xlValue).Select
        Selection.TickLabels.NumberFormat = "#,##0_ ;(#,##0)"
    End If


    Dashb.ChartObjects("CompChart1").Activate
    ActiveChart.Axes(xlValue).MinimumScale = Ind.Range("CD15")
    
    SendKeys "{ESC}"
    
End Sub


Private Sub ComboBox2_Change()
    Set Dashb = Sheets("Dashboard")
    Set Ind = Sheets("Index")
    
    Dashb.Range("BP34").Value = ComboBox2.Value


    If (Dashb.Range("BP34") = "LF" Or Dashb.Range("BP34") = "S3/Rev") Then
        Dashb.Range(Dashb.Cells(39, 69), Dashb.Cells(44, 81)).NumberFormat = "0.0%"
        Dashb.ChartObjects("CompChart2").Activate
        ActiveChart.Axes(xlValue).Select
        Selection.TickLabels.NumberFormat = "0.0%"
    ElseIf (Dashb.Range("BP34") = "CASK" Or Dashb.Range("BP34") = "RASK" Or Dashb.Range("BP34") = "Cost per Seat" Or Dashb.Range("BP34") = "Rev per Seat" Or Dashb.Range("BP5") = "Avg Fare") Then
        Dashb.Range(Dashb.Cells(39, 69), Dashb.Cells(44, 81)).NumberFormat = "#,##0.00_ ;(#,##0.00)"
        Dashb.ChartObjects("CompChart2").Activate
        ActiveChart.Axes(xlValue).Select
        Selection.TickLabels.NumberFormat = "#,##0.00_ ;(#,##0.00)"
    Else
        Dashb.Range(Dashb.Cells(39, 69), Dashb.Cells(44, 81)).NumberFormat = "#,##0_ ;(#,##0)"
        Dashb.ChartObjects("CompChart2").Activate
        ActiveChart.Axes(xlValue).Select
        Selection.TickLabels.NumberFormat = "#,##0_ ;(#,##0)"
    End If


    Dashb.ChartObjects("CompChart2").Activate
    ActiveChart.Axes(xlValue).MinimumScale = Ind.Range("CD44")
    
    SendKeys "{ESC}"
    
End Sub


Private Sub ComboBox3_Change()
    Set Dashb = Sheets("Dashboard")
    Set Ind = Sheets("Index")
    
    Ind.Range("B102").Value = ComboBox3.Value
    SendKeys "{ESC}"
End Sub


Private Sub ComboBox4_Change()
    Set Dashb = Sheets("Dashboard")
    Set Ind = Sheets("Index")
    
    Ind.Range("C103").Value = ComboBox4.Value
    
    If (Ind.Range("C103") = "Load Factor" Or Ind.Range("C103") = "S3/Rev") Then
        Dashb.Range(Dashb.Cells(74, 72), Dashb.Cells(93, 72)).NumberFormat = "#,##0.0%_ ;[Red](#,##0.0%)"
        Dashb.Range(Dashb.Cells(74, 74), Dashb.Cells(93, 74)).NumberFormat = "+#,##0.0 ""pts"" ;[Red](#,##0.0 ""pts)"""
        Dashb.Range(Dashb.Cells(74, 78), Dashb.Cells(93, 78)).NumberFormat = "+#,##0.0 ""pts"" ;[Red](#,##0.0 ""pts)"""
    ElseIf (Ind.Range("C103") = "No. Of AC") Then
        Dashb.Range(Dashb.Cells(74, 72), Dashb.Cells(93, 72)).NumberFormat = "#,##0.00_ ;[Red](#,##0.00)"
        Dashb.Range(Dashb.Cells(74, 74), Dashb.Cells(93, 74)).NumberFormat = "+#,##0.00_ ;[Red](#,##0.00)"
        Dashb.Range(Dashb.Cells(74, 78), Dashb.Cells(93, 78)).NumberFormat = "+#,##0.00_ ;[Red](#,##0.00)"
    Else
        Dashb.Range(Dashb.Cells(74, 72), Dashb.Cells(93, 72)).NumberFormat = "#,##0_ ;[Red](#,##0)"
        Dashb.Range(Dashb.Cells(74, 74), Dashb.Cells(93, 74)).NumberFormat = "+#,##0_ ;[Red](#,##0)"
        Dashb.Range(Dashb.Cells(74, 78), Dashb.Cells(93, 78)).NumberFormat = "+#,##0_ ;[Red](#,##0)"
    End If
    
    SendKeys "{ESC}"
End Sub


Private Sub ComboBox5_Change()
    Set Dashb = Sheets("Dashboard")
    Set Ind = Sheets("Index")
    
    Ind.Range("D102").Value = ComboBox5.Value
    SendKeys "{ESC}"
End Sub


Private Sub ComboBox6_Change()
    Set Dashb = Sheets("Dashboard")
    Set Ind = Sheets("Index")
    
    Ind.Range("E102").Value = ComboBox6.Value
    SendKeys "{ESC}"
End Sub


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("BR74")) Is Nothing Then
        If Range("BR74") <> "" Then
            Range("E4") = Range("BR74").Value
            Range("E4").Activate
        End If
    ElseIf Not Intersect(Target, Range("BR76")) Is Nothing Then
        If Range("BR76") <> "" Then
            Range("E4") = Range("BR76").Value
            Range("E4").Activate
        End If
    ElseIf Not Intersect(Target, Range("BR78")) Is Nothing Then
        If Range("BR78") <> "" Then
            Range("E4") = Range("BR78").Value
            Range("E4").Activate
        End If
    ElseIf Not Intersect(Target, Range("BR80")) Is Nothing Then
        If Range("BR80") <> "" Then
            Range("E4") = Range("BR80").Value
            Range("E4").Activate
        End If
    ElseIf Not Intersect(Target, Range("BR82")) Is Nothing Then
        If Range("BR82") <> "" Then
            Range("E4") = Range("BR82").Value
            Range("E4").Activate
        End If
    ElseIf Not Intersect(Target, Range("BR84")) Is Nothing Then
        If Range("BR84") <> "" Then
            Range("E4") = Range("BR84").Value
            Range("E4").Activate
        End If
    ElseIf Not Intersect(Target, Range("BR86")) Is Nothing Then
        If Range("BR86") <> "" Then
            Range("E4") = Range("BR86").Value
            Range("E4").Activate
        End If
    ElseIf Not Intersect(Target, Range("BR88")) Is Nothing Then
        If Range("BR88") <> "" Then
            Range("E4") = Range("BR88").Value
            Range("E4").Activate
        End If
    ElseIf Not Intersect(Target, Range("BR90")) Is Nothing Then
        If Range("BR90") <> "" Then
            Range("E4") = Range("BR90").Value
            Range("E4").Activate
        End If
    ElseIf Not Intersect(Target, Range("BR92    ")) Is Nothing Then
        If Range("BR92") <> "" Then
            Range("E4") = Range("BR92").Value
            Range("E4").Activate
        End If
    End If
End Sub

The problem is that the Worksheet_Change code does not work anymore.

It was working before I added all the other combo boxes and double click code. But now, even if I removed those subs, it's still not triggering anything.....

Anyone got any ideas of how I can fix this?

Cheers,

Eoin
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

J
  • Solved
Run time error for Worksheet Change in VBA
2
Replies
17
Views
430
jtemp57
J
R
  • Question Question
Worksheet_change with Public Variale
Replies
2
Views
635
ROUKWA
R
KlausW
  • Question Question
VBA code does not run properly with formulas
Replies
0
Views
431
KlausW
KlausW
L
  • Locked
  • Question Question
Changing Border formatting on another worksheet based off of values on active worksheet
Replies
1
Views
296
Fluff
Fluff
L
  • Solved
Changing Border formatting on another worksheet based off of values on active worksheet
Replies
3
Views
418
LukeFrost
L

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

Share this page

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