need help with VBA OuickPicks

[Captain Sensible]

It should give you an error message with an OK click box before highlighting the line.

[Captain Sensible]

And are all your sheets simply named sheet1, sheet2 etc It's usually something simple.

I put a reset macro in the file i put up so you can simply run that from the macro menu to reset things, that'll give you opportunity to test and refire error messages easier

[samhayer]

say Compile error Variable not defined

[Captain Sensible]

It should jump to the undefined variable before you click ok.

Is it giving you the error in the code you posted?

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If [A1] <> currentMarket Then
currentMarket = [A1]


Call Macro2
Call Macro4
Call Macro5
End If
End If
End Sub

[samhayer]

yes in the code I pasted

[samhayer]

its runs ok in the code I posted

[Captain Sensible]

This code?

Code: Select all

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If [A1] <> currentMarket Then
currentMarket = [A1]


Call Macro2
Call Macro4
Call Macro5
End If
End If
End Sub

Can't see any errors in there and it's the code you've been using before so shouldn't be now raising errors as it doesn't even contain refreshQPL stuff.

Did you try the sheet I did for you?

[samhayer]

no this code works okay the sheet runs okay
Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If [A1] <> currentMarket Then
currentMarket = [A1]


Call Macro2
Call Macro4
Call Macro5
End If
End If
End Sub
when I put the one I took from the sheet you sent me then it show error

[Captain Sensible]

Do you still have other code on that sheet like your logbalance routine? Under the Sheet1 object it should have nothing but

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False ' stopping events here mean we don't need to repeat it elsewhere
Application.Calculation = xlCalculationManual '

If Worksheets("Sheet1").Range("A1").Value <> currentMarket Then
currentMarket = Worksheets("Sheet1").Range("A1").Value ' usually best to write the full address in case the sheet is focussed on another sheet
logBalance
Call Macro2
Call Macro4
Call Macro5
End If

Call reloadQPL

Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub


Also do you have the reset macro in your module, it'll make life easier debugging for errors

Code: Select all

Sub reset()
End
Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub

[samhayer]

I dont have any other codes the logbalance has been removed .I dont have the reset where should I put the reset in a sheet or in mod1

[Captain Sensible]

Only the Worksheet_Change code I posted needs to be under Sheet1 everything else would go in the module/modules.

You don't need to remove the logbalance as that shouldn't be causing problems, alternatively you can simply replace it with a little code like

Code: Select all

Worksheets("Sheet2").Range("A2:B2").Insert (xlShiftDown)
Worksheets("Sheet2").Range("A2").Value = Worksheets("Sheet1").Range("A1").Value
Worksheets("Sheet2").Range("B2").Value = Worksheets("Sheet1").Range("I2").Value


It's not a problem.

Did you copy your macros, Macro2 etc, to the same module? It's possible they have undeclared variables, hard to say as I haven't seen them.

[samhayer]

right I put the reset code in mod1 and tried the code from your sheet again still show erroe ant it high lighted
Private Sub Worksheet_Change(ByVal Target As Range)
currentMarket

[Captain Sensible]

Did you copy all the code from the module, currentmarket was declared in that code

Code: Select all

    Option Explicit
    Public Today As Date, triggerQuickPickListReload As Boolean, triggerFirstMarketSelect As Boolean
    Public currentMarket As String  ' best to be a public variable so can be accessed by any routine


.........

[samhayer]

Dont know tried, no worry will have another go later thanks any way for trying to help me Cheers

[Captain Sensible]

No problem. When you're trying to track down errors you can simply disconnect the sheet from BA and comment out the line

Code: Select all

If Target.Columns.Count <> 16 Then Exit Sub

by putting a single quote in front of it

Code: Select all

'If Target.Columns.Count <> 16 Then Exit Sub

That then means you can just change any cell on the sheet i.e stick 1 in A1 and the code will fire as if it was connected to BA. It means you can track down problems easier than having it connected to BA, any errors that arise you can try to correct then run the Reset macro and try again until you've sorted any error.

Don't forget to remove the single quote from If Target.Columns.Count <> 16 Then Exit Sub when reconnecting to BA though.