Userform Textbox Behaviour

**payney** · 07-27-2010, 09:10 AM

Hi all,

This is my first post, but I've been helped 100's of times by the site!

Right, I've got a userform I'm using as a control panel, it comes up automatically when the workbook opens. I've put a bunch of buttons on their which are cool, and I've got pretty much everything I want on there working fine, staying visible at all times.

I had put a little text box just saying 'Welcome' in the body of the userform, but I've been asked whether I can change that into a kind of message centre depending on what worksheet tab is open.

For example, if Sheet 1 is open, the text on the control panel says 'Here's some help for Sheet 1', if the user then clicks to open Sheet 2 the text changes to say 'Here's some help for Sheet 2'. Is there any coding I can use to change the text depending on what tab is open? I was thinking some kind of ActiveWorksheet tab or something? But all the ones I've tried to create on my own fail!

The only way I can think of doing it is duplicating my whole userform over and over and then having, effectively, a different userform opening on each worksheet tab. But that would take me forever to copy all the button codes and design of the form itself... Is there an easier way????

Thanks for any help in advance...

**romperstomper** · 07-27-2010, 09:25 AM

You basically need

Please Login or Register  to view this content.

How you apply that would depend on whether the form is modal (simple - just use the Initialize or Activate event) or modeless (trickier as you need to trap the user switching sheets while the form is open).

**MarvinP** · 07-27-2010, 09:49 AM

Put the following code behind each worksheet and simply change the text message.

Private Sub Worksheet_Activate()
UserForm1.TextBox1.Text = "This is Sheet number 2!!"
End Sub

When the worksheet is activates the text on your userform will change.