Hide a textbox on open of Excel

[brittonsm]

I want to place a textbox over a few buttons telling the user they
must enable their macros for the worksheet to function. Once they
enable macros I'd like the textbox to hide itself - OnOpen I'm
thinking....

Then after the fiddle with the sheet and make updates I'd like to have
the textbox reappear for the next use BeforeClose probably.

I have this but it doesn't work:

Private Sub Workbook_Open()
TextBox1.Visible = False
End Sub


Thanks

 

[Rick Rothstein]

The opening of the Workbook may be too early in the start up process... what
happens if you try that code in the Activate event for the worksheet that
the TextBox is on?

 

[Peter T]

Looks like that code is in the thisworkbook module so you'll need to qualify
TextBox1 with the sheet. Following for ideas, code in three modules -

' worksheet module

Public Sub ShowObject(bShow As Boolean)
On Error Resume Next
Me.TextBox1.Visible = bShow
End Sub

' thisworkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Sheet1.ShowObject True
Application.OnTime Now, "ReShow"
End Sub

Private Sub Workbook_Open()
Sheet1.ShowObject False
End Sub

'normal module
Sub ReShow()
Dim bSaved As Boolean
bSaved = ThisWorkbook.Saved
Sheet1.ShowObject False
ThisWorkbook.Saved = bSaved
End Sub

Regards,
Peter T

 

[brittonsm]

Peter

This work perfectly thanks!

-Steve

Looks like that code is in the thisworkbook module so you'll need to qualify
TextBox1 with the sheet. Following for ideas, code in three modules -

' worksheet module

Public Sub ShowObject(bShow As Boolean)
    On Error Resume Next
    Me.TextBox1.Visible = bShow
End Sub

' thisworkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
    Sheet1.ShowObject True
    Application.OnTime Now, "ReShow"
End Sub

Private Sub Workbook_Open()
    Sheet1.ShowObject False
End Sub

'normal module
Sub ReShow()
Dim bSaved As Boolean
    bSaved = ThisWorkbook.Saved
    Sheet1.ShowObject False
    ThisWorkbook.Saved = bSaved
End Sub

Regards,
Peter T










- Show quoted text -