Floating Window - Playing with Scenarios

  • Thread starter Thread starter RobFJ
  • Start date Start date
R

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
 
Thanks Dave - but too advanced for me to know how to change from the example
to what I want to do
 
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.
 
Back
Top