Changing font colour

[dugdugdug]

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:

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

.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

 

[dugdugdug]

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.

Rings a bell now when people say writing code in Worksheets and Userforms is sort of like using Class Modules because you must instantiate before doing anything, so as you said, as the Worksheet doesn't have my method, I must instantiate first before calling it.

But isn't it easier just to use Me instead?

After all, it's defined as an implicitly declared variable.

 

[RoryA]

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

Actually quite the reverse!

If both args are strings, then you can use Range and it won't error (although it may refer to the wrong sheet) but if both are range arguments then you should use .Range

Consider this code in Sheet2 code module

with sheet1
   msgbox Range(.range("A1"), .Range("A10")).Address
End With

Any attempt to run this code will fail because in a worksheet code module Range is the equivalent of Me.Range and not Application.Range as it would be in a normal module. The code should be

with sheet1
   msgbox .Range(.range("A1"), .Range("A10")).Address
End With

 

[RoryA]

But isn't it easier just to use Me instead?

If the code is in the worksheet (or class) code module then yes you should always use Me

 

[ZVI]

Actually quite the reverse!

Yes. of course if it is inside the sheet's (class) module!
My bad - have not read all discussion...
I was sure it's all about the code in general module, what my 5 cents was for