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

 

[Darren Smith]

Yes it does

 

[Darren Smith]

If it's not running that line, then that's why you aren't seeing any colours. Does it run the line before it?

Yes it does

 

[RoryA]

Then there should be no reason it doesn't run the line that sets the colour. There is nothing in the previous line that does anything other than changing a variable value.

 

[Darren Smith]

Can I send the workbook to you so you can see exactly what is going on. Via Dropbox

 

[Darren Smith]

Can anybody suggest a way of doing the above more simply??

 

[RoryA]

Can I send the workbook to you so you can see exactly what is going on. Via Dropbox

Upload it and put a link here.

 

[Darren Smith]

Hi RoryA

This is the Workbook for you to look at

Automated Cardworker.xlsm​

Shared with Dropbox

www.dropbox.com

 

[jasonb75]

There is a lot of stuff in that workbook, any chance of a clue? What do we need to do to trigger the code?

I found the form that it relates to and tried to show it manually but it resulted in an error, so looks like it needs to be loaded properly.

edit:- scrap that, I just found the line that opens a folder on your computer. Maybe Rory will find something, but I can't see that we can test it properly without the files and folders that it is trying to load.

 

[Darren Smith]

Can i send the files & folders to you?

 

[Darren Smith]

If i run f8 on the code below it seems miss out the last 2 lines of the function code see below.

 EmptyRowNum = 0
        row.Interior.ColorIndex = 6

If i select the

Case ("Break Lines 3 Page Job Card")

it just finds the first Range but not the other 2 Ranges shown below?

Color .Range("A66:Q122")
                   Color .Range("A127:Q178")

Also it says

 Empty row = 0

when it should say 136

Hope this helps?