How to change a Label.Caption with Worksheet_Activate


New Member
Mar 5, 2009
I'm having difficulty getting my Worksheet_Activate to change my Label.Caption when the worksheet is activated and need some help.

I'm using two worksheets.
One with DATA in Cells and Command Button.
The second sheet is a form that takes the data
and put it into Labels, then prints out the form.
After the print, I return to the First page get the
next record and return to the form and print it out.

If I manually start with sheet 1, then manually
select sheet 2, the Label.Captions are updated.
If I do this from a Command Button they do not

'----Sheet one------------------------------------------
Private Sub CommandGameSheetPreview_Click()
Dim StartRow As Integer
Dim EndRow As Integer
Dim Msg As String
Dim i As Integer
StartRow = Range("E35")
EndRow = Range("E36")
If StartRow > EndRow Then
Msg = "ERROR" & vbCrLf & "The First Game Sheet be less than the Last Game Sheet!"
MsgBox Msg, vbCritical, APPNAME
End If
Call PageLayout
ActiveSheet.Unprotect "ora"
Sheet = ActiveSheet.Name
Game_Number = "='" & ActiveSheet.Name & "'!R34C7"
Game_Date = "='" & ActiveSheet.Name & "'!R35C7"
Game_Time = "='" & ActiveSheet.Name & "'!R36C7"
Home_Team = "='" & ActiveSheet.Name & "'!R34C9"
Away_Team = "='" & ActiveSheet.Name & "'!R35C9"
Division = "='" & ActiveSheet.Name & "'!R35C11"
Arena = "='" & ActiveSheet.Name & "'!R36C11"
ActiveWorkbook.Names.Add Name:="Game_Number", RefersToR1C1:=Game_Number
ActiveWorkbook.Names.Add Name:="Game_Date", RefersToR1C1:=Game_Date
ActiveWorkbook.Names.Add Name:="Game_Time", RefersToR1C1:=Game_Time
ActiveWorkbook.Names.Add Name:="Home_Team", RefersToR1C1:=Home_Team
ActiveWorkbook.Names.Add Name:="Away_Team", RefersToR1C1:=Away_Team
ActiveWorkbook.Names.Add Name:="Division", RefersToR1C1:=Division
ActiveWorkbook.Names.Add Name:="Arena", RefersToR1C1:=Arena
For i = StartRow To EndRow
Range("E34") = i
Sheets("Game Sheet").Select
Sheets("3 games - 4 teams").Select
Next i
ActiveSheet.Protect "ora"
End Sub
'----Sheet two------------------------------------------
Private Sub Worksheet_Activate()
MsgBox "Beginning of Activate"
Label_Game_Number.Caption = Range("A1").Text
Label_Division.Caption = Range("C1").Text
Label_Title.Caption = Range("E1").Text
Label_Game_Time.Caption = Range("U1").Text
Label_Game_Date.Caption = Range("S1").Text
Label_Arena.Caption = Range("L1").Text
Label_Home_Team.Caption = Range("R1").Text
Label_Away_Team.Caption = Range("O1").Text
MsgBox "End of Activate"
End Sub
As I said, the Worksheet_Activate() works
both manually (when I select the sheet tab)
and from the Command Button, but all the
Label.Caption do not....

I know this because the MsgBox's work fine.

Any Idea's whould be a great help
Last edited by a moderator:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.


Board Regular
Feb 20, 2009
try like
sheets("sheet2").OLEObjects("label4").object.caption = "some text"
change sheet and label name to suit


New Member
Mar 5, 2009
The code work if I select the worksheet tab but not if I use a Command_Button.

It's as if the Work_Sheet is working too fast for the Label.Caption to kick in.

In fact If I put a msgbox between each Label.Caption command.
The eash come up, but the caption does not work.????

Any thoughts?

Watch MrExcel Video

Forum statistics

Latest member
Ernest F Mink

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...