Changing font colour

dugdugdug

Active Member
Joined
May 11, 2012
Messages
342
I have a graph on a worksheet (Sheet1) and data associated with it.

There is a button called Data_btn to show or hide the data and this works by simply changing the colour of the font of the data to "hide" it. (Hiding the column entirely will make the graph show nothing, as expected).

This is my code stored in Sheet1:

Code:
Private Sub Data_btn_Click()

Application.ScreenUpdating = False
    
With Sheet1

    Select Case Data_btn.Caption

        Case "Show Data"
   
           Range(.Columns(1), .Columns(2)).Font.ColorIndex = xlAutomatic
      
           .Data_btn.Caption = "Hide Data"

        Case Else
            
            Range(.Columns(1), .Columns(2)).Font.ColorIndex = 2
            
            .Data_btn.Caption = "Show Data"

    End Select

End With
    
Set myworksheet = Nothing
    
Application.ScreenUpdating = True

Exit Sub

The code works except it hangs for a few seconds on these lines (and then continues to the end):

Code:
.Data_btn.Caption = "Hide Data"

.Data_btn.Caption = "Show Data"

What is the reason for the delay?

Could it be due to the size of the data (sometimes there are 30,000 rows)?

Thanks
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
I get no delay with that code in Excel 2003, although I do get a compile error on this line with Option Explicit set:

Code:
Set myworksheet = Nothing

What other code do you have in your workbook?
 
Upvote 0
You might try a Forms button instead and see if that's faster.

On a side note this:
Code:
Range(.Columns(1), .Columns(2))
is a dangerous habit to get into. It should be
Code:
.Range(.Columns(1), .Columns(2))
and you can replace Sheet1 with Me since you are referring to the sheet containing the code (then your code will still work if you change the sheet codename to something more meaningful)
 
Upvote 0
I get no delay with that code in Excel 2003, although I do get a compile error on this line with Option Explicit set:

Code:
Set myworksheet = Nothing

What other code do you have in your workbook?

Sorry that was an error.

Initially I wrote Set myworksheet = Sheet1 but then deleted it.

If you're talikng about related code, there isn't any.
 
Last edited:
Upvote 0
You might try a Forms button instead and see if that's faster.

On a side note this:
Code:
Range(.Columns(1), .Columns(2))
is a dangerous habit to get into. It should be
Code:
.Range(.Columns(1), .Columns(2))
and you can replace Sheet1 with Me since you are referring to the sheet containing the code (then your code will still work if you change the sheet codename to something more meaningful)

Thanks, will try the forms button.

On your note on Me, I agree but actually this was what I originally wrote:

Code:
With Sheet1

    With Range(.Columns(1), .Columns(2)).Font
    
        Select Case Data_btn.Caption
    
            Case "Show Data"
       
               .ColorIndex = xlAutomatic
          
               Data_btn.Caption = "Hide Data"
    
            Case Else
                
               .ColorIndex = 2
                
                Data_btn.Caption = "Show Data"
    
        End Select
    
    End With
    
End With

ie adding another with to "take care of" Range(.Columns(1), .Columns(2)).Font but then .Data_btn.Caption doesn't work because it should be Sheet6.Data_btn and not Sheet6.Range(.Columns(1), .Columns(2)).Font.Data_btn.Caption!

So my question is which is better or is it 6 of one and half a dozen of the other?
 
Upvote 0
You might try a Forms button instead and see if that's faster.

On a side note this:
Code:
Range(.Columns(1), .Columns(2))
is a dangerous habit to get into. It should be
Code:
.Range(.Columns(1), .Columns(2))
and you can replace Sheet1 with Me since you are referring to the sheet containing the code (then your code will still work if you change the sheet codename to something more meaningful)

Not sure if you recall another thread I started, where I said I had to write Call Sheet1.MyProgram and not Call wsg.MyProgram even though I had Set wsg = Worksheets("Graph")?

You said the solution was;

Code:
Set myworksheet As Sheet1

Call myworksheet.MyProgram


That's fine so here I wrote:

Code:
With wsg

    With .Range blah blah blah

It works but should it because I DIDN'T write:

Code:
Set myworksheet = Worksheet"Graph")

With myworksheet

    .Range blah blah blah
 
Last edited:
Upvote 0
It's 6 of one... ;)
But it still should be .Range and not just Range

How did you assign wsg (or is that a sheet codename)?
 
Upvote 0
It's 6 of one... ;)
But it still should be .Range and not just Range

How did you assign wsg (or is that a sheet codename)?

Agree.

In a module, ModInitialise, I put:

Code:
Global wb As Workbook
Global wsg As Worksheet

Sub Initialise()

Set wb = ThisWorkbook
Set wsg = wb.Worksheets("Graph")

End Sub

I declared it as Global (could've used Public) because I use it many times in different Modules in my program.

As you said in a previous thread, I cannot write this in a worksheet (say Sheet1, which is called Graph):

Code:
Call wsg.MyProgram

where MyProgram is a procedure within Sheet1.

Instead:

Code:
Dim myworksheet as Sheet1

Set myworksheet = Sheet1

Call myworksheet.MyProgram

Set myworksheet = Nothing

But after reading this thread I could (and should) write:

Code:
Call Me.MyProgram
 
Last edited:
Upvote 0
I think you're getting confused. ;)wsg.range(...)works because the worksheet class does have a Range property (all worksheets have ranges). The problem in the previous question was that the worksheet class doesn't have the method you were trying to call (not all worksheets have your custom code in them of course) and that is why you couldn't declare the variable as Worksheet.
 
Upvote 0
But it still should be .Range and not just Range
It should in case of usage string address as argument.
But till all arguments are Range objects, like in this case, either of .Range or Range is correct.
But the "should be" rule is good for the safety reason :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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