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

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
417
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,714
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
As far as I can see there aren't any empty rows in those ranges which is why you don't see any colours. There are formulas in pretty much every cell in column P - the ones that look blank are actually returning 0 but you have turned off the display of 0 values in the sheet.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,714
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Also, you have an awful lot of With blocks in your code that are not actually doing anything. The whole point of a With construct is that it holds a reference to an object so that you don't have to repeat that object within the block. As an example, this:

VBA Code:
With Me.ListBox4
ListBox4.AddItem "Fill Details"
ListBox4.AddItem "Formatting & Formula Update"
ListBox4.AddItem "Save Jobcard"
ListBox4.AddItem "Open Job Record"
ListBox4.AddItem "Open TGS Group Inventory"
ListBox4.AddItem "Open Jobcard & Drawing Matrix"
ListBox4.AddItem "Open Drawing List"
ListBox4.AddItem "Open SOPS"
ListBox4.AddItem "Quote and Job Details to Jobcard Analysis Sheet"
ListBox4.AddItem "1 Page Job Card Master Details to Jobcard Analysis Sheet"
ListBox4.AddItem "2 Page Job Card Master Details to Jobcard Analysis Sheet"
ListBox4.AddItem "3 Page Job Card Master Details to Jobcard Analysis Sheet"
ListBox4.AddItem "4 Page Job Card Master Details to Jobcard Analysis Sheet"
ListBox4.AddItem "5 Page Job Card Master Details to Jobcard Analysis Sheet"
ListBox4.AddItem "Print Jobcard"

End With

is a pointless With block because you repeated the object reference in every line. It should be this:

VBA Code:
With Me.ListBox4
.AddItem "Fill Details"
.AddItem "Formatting & Formula Update"
.AddItem "Save Jobcard"
.AddItem "Open Job Record"
.AddItem "Open TGS Group Inventory"
.AddItem "Open Jobcard & Drawing Matrix"
.AddItem "Open Drawing List"
.AddItem "Open SOPS"
.AddItem "Quote and Job Details to Jobcard Analysis Sheet"
.AddItem "1 Page Job Card Master Details to Jobcard Analysis Sheet"
.AddItem "2 Page Job Card Master Details to Jobcard Analysis Sheet"
.AddItem "3 Page Job Card Master Details to Jobcard Analysis Sheet"
.AddItem "4 Page Job Card Master Details to Jobcard Analysis Sheet"
.AddItem "5 Page Job Card Master Details to Jobcard Analysis Sheet"
.AddItem "Print Jobcard"

End With

although I would personally use:

VBA Code:
Me.ListBox4.List = Array("Fill Details", "Formatting & Formula Update", "Save Jobcard", "Open Job Record", "Open TGS Group Inventory", "Open Jobcard & Drawing Matrix", "Open Drawing List", _
 "Open SOPS", "Quote and Job Details to Jobcard Analysis Sheet", "1 Page Job Card Master Details to Jobcard Analysis Sheet", "2 Page Job Card Master Details to Jobcard Analysis Sheet", _
 "3 Page Job Card Master Details to Jobcard Analysis Sheet", "4 Page Job Card Master Details to Jobcard Analysis Sheet", "5 Page Job Card Master Details to Jobcard Analysis Sheet", "Print Jobcard")
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
417
Office Version
  1. 2019
Platform
  1. Windows
As far as I can see there aren't any empty rows in those ranges which is why you don't see any colours. There are formulas in pretty much every cell in column P - the ones that look blank are actually returning 0 but you have turned off the display of 0 values in the sheet.
What would you say to deleting all formulas then add them back in at the end of the code?
Else would you say all empty cells in a Column C count then put a Row of color above filled in cell?
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,714
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I don't know - it's your workbook. ;)
 

Darren Smith

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

ADVERTISEMENT

I`ve done the first option but it delete`s the color from cells when I add the formulas back in and idea how to overcome this?
Also it`s filling color rows down to bottom of spreadsheet needs to stop at last filled in row.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,714
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I don't follow - I thought the whole point of this was to highlight blank rows in specific ranges.
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
417
Office Version
  1. 2019
Platform
  1. Windows
I don't follow - I thought the whole point of this was to highlight blank rows in specific ranges.
Sorry the idea was to fill a blank row above the filled in rows but stop at the last filled in row in specified Ranges?
Other ques how do you fill color without interfering with the formulas. If I add formulas back in the cell colors in column P disappear?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,714
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Formulas and colours are not related, but if you copy/paste or fill a cell down, that cell's formatting comes with it (unless you do a paste special).
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
417
Office Version
  1. 2019
Platform
  1. Windows
I am trying to not have to copy and paste just need color to fill over the cells.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,898
Messages
5,638,886
Members
417,058
Latest member
BRYCEPIETROWIAK

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