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

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,671
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,671
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
Thanks very much for explaining the above..

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,671
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Then you need to post the code for the Color routine. Perhaps you have an End statement in there.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,155
Messages
5,640,448
Members
417,143
Latest member
boukadidanizar

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
Top