Floating Window - Playing with Scenarios

[RobFJ]

I've got a spreadsheet that comprises about ten worksheets. The spreadsheet
allows for about five different scenarios to be reviewed.

At the moment to change scenarios, I have to change a cell in a particular
worksheet.

But what I want to be able to do is have a small window (that always stays
on top of the open worksheet) where I can switch scenarios by changing the
number in that window. That way I can see the data in the worksheet change.

Can SKS help - or suggest an alternative approach.

Tx

Rob

 

[RobFJ]

Should have added - it's excel 2007


news:[email protected]...

 

[Dave Peterson]

Maybe you can design a small(?) userform that you can show non-modally (so that
you can still edit cells on the worksheet).

If you want to design your own userform:

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp

 

[RobFJ]

Thanks Dave - but too advanced for me to know how to change from the example
to what I want to do

 

[Dave Peterson]

I'm not sure how much you need, but this may get you started.

Create a userform with a single textbox and two commandbuttons on it.

Place this code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Dim myCell As Range
'change the sheetname and the address here
Set myCell = Worksheets("SomeSheetNameHere").Range("A1")
myCell.Value = Me.TextBox1.Value
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.Caption = "Ok"
.Default = True
End With
With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
End With
End Sub

Then insert a general module to show the userform and put this code in that
module.

Option Explicit
Sub ShowTheForm()
UserForm1.Show False
End Sub

Now use Alt-F8 to invoke this macro and see if it works.

If you didn't read Debra's notes, take some time to review them.

 

[RobFJ]

That's great Dave - thanks a lot - it works a treat

 

[Dave Peterson]

Glad it worked ok.

That's great Dave - thanks a lot - it works a treat