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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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 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

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
Can I send the workbook to you so you can see exactly what is going on. Via Dropbox
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi RoryA

This is the Workbook for you to look at

www.dropbox.com

Automated Cardworker.xlsm

Shared with Dropbox
www.dropbox.com
www.dropbox.com
 

jasonb75

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

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
If i run f8 on the code below it seems miss out the last 2 lines of the function code see below.
VBA Code:
 EmptyRowNum = 0
        row.Interior.ColorIndex = 6

If i select the
VBA Code:
Case ("Break Lines 3 Page Job Card")
it just finds the first Range but not the other 2 Ranges shown below?
VBA Code:
Color .Range("A66:Q122")
                   Color .Range("A127:Q178")

Also it says
VBA Code:
 Empty row = 0
when it should say 136

Hope this helps?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,152
Messages
5,640,426
Members
417,142
Latest member
andygame

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