Can`t seem to get my code to recognize my SpreadSheet

[Darren Smith]

Problem is the worksheet for some reason will not work. It says ws = Nothing?? I have set it up same as before baffled.
I tried to set a workbook variable Then using Thisworkbook.Sheets still no luck.
Don`t Know of any other way it could or should work

Private Sub Add_Break_Lines_Click()

                              
                Dim Com As ComboBox
                Dim ws As Worksheet
                Dim wb As Workbook
                
                
                Set wb = Workbooks("Automated Cardworker")
                Set ws = wb.Worksheets("Job Card Master")
                Set Com = Me.Add_Break_Lines
                

            
               With ws
               
                Select Case Com.Value
                    Case ("Break Lines 1 Page Job Card")
                        Color .Range("A13:Q61")
            
                    Case ("Break Lines 2 Page Job Card")
                        Color .Range("A13:Q61")
                        Color .Range("A66:Q120")
               
                    Case ("Break Lines 3 Page Job Card")
                         Color .Range("A13:Q61")
                        Color .Range("A66:Q122")
                        Color .Range("A127:Q178")
                        
                    Case ("Break Lines 4 Page Job Card")
                        Color .Range("A13:Q61")
                        Color .Range("A66:Q122")
                        Color .Range("A127:Q183")
                        Color .Range("A188:Q244")
            
                    Case ("Break Lines 5 Page Job Card")
                        Color .Range("A13:Q61")
                        Color .Range("A66:Q122")
                        Color .Range("A127:Q183")
                        Color .Range("A188:Q244")
                        Color .Range("A249:Q299")
                End Select
                
            End With

            End Sub

 

[jasonb75]

Are you getting runtime error 9, subscript out of range, highlighting the line Set ws = wb.Worksheets("Job Card Master") ?
If yes, it would suggest a typo in the sheet name.
If something else, then what exactly? An empty variable could just mean that you looked before it was filled and that something else is stopping the code reaching that point.

 

[Darren Smith]

Are you getting runtime error 9, subscript out of range, highlighting the line Set ws = wb.Worksheets("Job Card Master") ?
If yes, it would suggest a typo in the sheet name.
If something else, then what exactly? An empty variable could just mean that you looked before it was filled and that something else is stopping the code reaching that point. I think your right on this

I`ve copied the sheet name from sheet properties in VBA so it must be right?
If I use F8 test it runs but it does not give run time error 9. It just goes through the code without any problem but for some reason does not add color lines to job sheet?

 

[jasonb75]

There is no On Error Resume Next in the code so that means that if the sheet name was wrong then it would show a runtime error.

Anything stopping the code reaching the point where it the sheet is set to the variable would not allow the code to run at all, you would get an error message when it tries to initialise, with a blue highlight on the problem line instead of the usual yellow runtime error highlight.

Note that when you step through, the variable will show empty / nothing if you hover over it before the code has stepped past the line where it is set. Stopping on the line means that the variable has not yet been set.

If you can step through fine with no errors then it simply means that something is aiming at the wrong place, perhaps the 'color' code is set to a different sheet?

 

[RoryA]

When using f8 does it actually run through one of the lines that colours a range?

 

[Darren Smith]

Thanks very much for explaining the above..

How can I make sure that the color code is set to the right sheet?

 

[jasonb75]

When using f8 does it actually run through one of the lines that colours a range?

Good point, I was focused on the sheet name and didn't even think to check that a valid case was being identified.

 

[Darren Smith]

When using f8 does it actually run through one of the lines that colours a range?

It does run through one of the lines but not all the lines it needs to?
Example if I choose Breaklines 2 Page Job Card it only goes to Color .Range("A13:Q61") but not, Color .Range("A66:Q120")

 

[RoryA]

Then you need to post the code for the Color routine. Perhaps you have an End statement in there.

 

[Darren Smith]

I think the Color routine is above in my first post