How to change a Label.Caption with Worksheet_Activate

AjaxOpenB

New Member
Joined
Mar 5, 2009
Messages
2
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

Code:
'----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.

westconn1

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

AjaxOpenB

New Member
Joined
Mar 5, 2009
Messages
2
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

Threads
1,101,802
Messages
5,482,986
Members
407,371
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...
  • CELL FORMAT - IF CONDITION
    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...
Top