Hi there

I've got this combo box set up so that when I pick one scenario, it
executes a macro which changes a few values on the current sheet.
However, this is a problem if I change worksheets and then change the
scenario, as it updates the values on that sheet. What I'm looking to
do is change the scenario and execute the macro to change ONLY the
values on the tab that I want?

For example, I'm in Profits worksheet and choose best scenario from
the combo box, and it changes B92 in Rev tab. Also, is there a way to
keep focus on the worksheet and have it still execute the macro?

Any help would be greatly appreciated!

hi

a little confuse what you said.

you mean you have 2 tabs, 1 is Profits, 1 is Rev. a combo box in Profits
which will change B92 in Rev when changed.

I don't know what you mean keep focus on the worksheet?

How do you configure for this combo box to change the value in Rev B92 ?

do you use VBA code? if yes, what's your code?




" wrote:

Hi there

I've got this combo box set up so that when I pick one scenario, it
executes a macro which changes a few values on the current sheet.
However, this is a problem if I change worksheets and then change the
scenario, as it updates the values on that sheet. What I'm looking to
do is change the scenario and execute the macro to change ONLY the
values on the tab that I want?

For example, I'm in Profits worksheet and choose best scenario from
the combo box, and it changes B92 in Rev tab. Also, is there a way to
keep focus on the worksheet and have it still execute the macro?

Any help would be greatly appreciated!

not sure what you mean either:

1. when you execute the code on the combobox change, activate the sheet you want
then execute the code
2. qualify the ranges with the sheet name, so when the code executes, it always
references the cells that sheet.

--


Gary


wrote in message
oups.com...
Hi there

I've got this combo box set up so that when I pick one scenario, it
executes a macro which changes a few values on the current sheet.
However, this is a problem if I change worksheets and then change the
scenario, as it updates the values on that sheet. What I'm looking to
do is change the scenario and execute the macro to change ONLY the
values on the tab that I want?

For example, I'm in Profits worksheet and choose best scenario from
the combo box, and it changes B92 in Rev tab. Also, is there a way to
keep focus on the worksheet and have it still execute the macro?

Any help would be greatly appreciated!

yes, that is correct. I have the combo box always present, so that
even if I change tabs, I have the option of choosing a scenario.
Currently, when I pick a scenario, the macro is executed on the
current worksheet. This is no good, as it overwrites things that are
there. So I need the macro to change only the cells in the rev
worksheet. I'm thinking I need to change the lines inside the macro
to have the rev title in front of the cell, but I'm not sure how to do
this. Here's my code for the rev page

Private Sub ComboBox1_Change()
Select Case Me.ComboBox1.Value
Case "basic": Call base
Case "increased": Call Increased
Case "decreased": Call Reduced
Case "superior": Call Superior
Case "inferior": Call Inferior
End Select
End Sub

Private Sub Worksheet_Activate()
cboScenario.Show vbModeless
End Sub


-----------------

here is my code for the macro called basic (which is an option in the
combo box)

Sub base()
'
' base Macro
' Macro recorded 4/16/2007 by User
'

'
Range("B83").Select
ActiveCell.FormulaR1C1 = "415"
Range("B84").Select
ActiveCell.FormulaR1C1 = "235"
Range("B85").Select
ActiveCell.FormulaR1C1 = "50"
Range("B86").Select
ActiveCell.FormulaR1C1 = "25"
Range("B87").Select
ActiveCell.FormulaR1C1 = "75"
Range("B88").Select
ActiveCell.FormulaR1C1 = "70"
Range("B89").Select
ActiveCell.FormulaR1C1 = "98"
Range("B90").Select
ActiveCell.FormulaR1C1 = "126"
Range("A90").Select
ActiveCell.FormulaR1C1 = "Occupancy-90% -3RD "
Range("A89").Select
ActiveCell.FormulaR1C1 = "Occupancy-70% -2ND"
Range("A88").Select
ActiveCell.FormulaR1C1 = "Occupancy-50% -1ST 4 MO."

End Sub

unless i'm missing something, i'm guessing this code always modifies the same
sheet, rev?

so try this:


Sub base()
'
' base Macro
' Macro recorded 4/16/2007 by User
'

With Worksheets("rev")
.Range("B83").Value = "415"
.Range("B84").Value = "235"
.Range("B85").Value = "50"
.Range("B86").Value = "25"
.Range("B87").Value = "75"
.Range("B88").Value = "70"
.Range("B89").Value = "98"
.Range("B90").Value = "126"
.Range("A90").Value = "Occupancy-90% -3RD "
.Range("A89").Value = "Occupancy-70% -2ND"
.Range("A88").Value = "Occupancy-50% -1ST 4 MO."
End With
End Sub

--


Gary


wrote in message
ps.com...
yes, that is correct. I have the combo box always present, so that
even if I change tabs, I have the option of choosing a scenario.
Currently, when I pick a scenario, the macro is executed on the
current worksheet. This is no good, as it overwrites things that are
there. So I need the macro to change only the cells in the rev
worksheet. I'm thinking I need to change the lines inside the macro
to have the rev title in front of the cell, but I'm not sure how to do
this. Here's my code for the rev page

Private Sub ComboBox1_Change()
Select Case Me.ComboBox1.Value
Case "basic": Call base
Case "increased": Call Increased
Case "decreased": Call Reduced
Case "superior": Call Superior
Case "inferior": Call Inferior
End Select
End Sub

Private Sub Worksheet_Activate()
cboScenario.Show vbModeless
End Sub


-----------------

here is my code for the macro called basic (which is an option in the
combo box)

Sub base()
'
' base Macro
' Macro recorded 4/16/2007 by User
'

'
Range("B83").Select
ActiveCell.FormulaR1C1 = "415"
Range("B84").Select
ActiveCell.FormulaR1C1 = "235"
Range("B85").Select
ActiveCell.FormulaR1C1 = "50"
Range("B86").Select
ActiveCell.FormulaR1C1 = "25"
Range("B87").Select
ActiveCell.FormulaR1C1 = "75"
Range("B88").Select
ActiveCell.FormulaR1C1 = "70"
Range("B89").Select
ActiveCell.FormulaR1C1 = "98"
Range("B90").Select
ActiveCell.FormulaR1C1 = "126"
Range("A90").Select
ActiveCell.FormulaR1C1 = "Occupancy-90% -3RD "
Range("A89").Select
ActiveCell.FormulaR1C1 = "Occupancy-70% -2ND"
Range("A88").Select
ActiveCell.FormulaR1C1 = "Occupancy-50% -1ST 4 MO."

End Sub

On May 1, 1:52 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
unless i'm missing something, i'm guessing this code always modifies the same
sheet, rev?

so try this:

Sub base()
'
' base Macro
' Macro recorded 4/16/2007 by User
'

With Worksheets("rev")
.Range("B83").Value = "415"
.Range("B84").Value = "235"
.Range("B85").Value = "50"
.Range("B86").Value = "25"
.Range("B87").Value = "75"
.Range("B88").Value = "70"
.Range("B89").Value = "98"
.Range("B90").Value = "126"
.Range("A90").Value = "Occupancy-90% -3RD "
.Range("A89").Value = "Occupancy-70% -2ND"
.Range("A88").Value = "Occupancy-50% -1ST 4 MO."
End With
End Sub

--

Gary

wrote in message

ps.com...

yes, that is correct. I have the combo box always present, so that
even if I change tabs, I have the option of choosing a scenario.
Currently, when I pick a scenario, the macro is executed on the
current worksheet. This is no good, as it overwrites things that are
there. So I need the macro to change only the cells in the rev
worksheet. I'm thinking I need to change the lines inside the macro
to have the rev title in front of the cell, but I'm not sure how to do
this. Here's my code for the rev page

Private Sub ComboBox1_Change()
Select Case Me.ComboBox1.Value
Case "basic": Call base
Case "increased": Call Increased
Case "decreased": Call Reduced
Case "superior": Call Superior
Case "inferior": Call Inferior
End Select
End Sub

Private Sub Worksheet_Activate()
cboScenario.Show vbModeless
End Sub

-----------------

here is my code for the macro called basic (which is an option in the
combo box)

Sub base()
'
' base Macro
' Macro recorded 4/16/2007 by User
'

'
Range("B83").Select
ActiveCell.FormulaR1C1 = "415"
Range("B84").Select
ActiveCell.FormulaR1C1 = "235"
Range("B85").Select
ActiveCell.FormulaR1C1 = "50"
Range("B86").Select
ActiveCell.FormulaR1C1 = "25"
Range("B87").Select
ActiveCell.FormulaR1C1 = "75"
Range("B88").Select
ActiveCell.FormulaR1C1 = "70"
Range("B89").Select
ActiveCell.FormulaR1C1 = "98"
Range("B90").Select
ActiveCell.FormulaR1C1 = "126"
Range("A90").Select
ActiveCell.FormulaR1C1 = "Occupancy-90% -3RD "
Range("A89").Select
ActiveCell.FormulaR1C1 = "Occupancy-70% -2ND"
Range("A88").Select
ActiveCell.FormulaR1C1 = "Occupancy-50% -1ST 4 MO."

End Sub

Thank you so much! This is EXACTLY what I needed! I can't thank you
enough!

welcome

--


Gary


wrote in message
ups.com...
On May 1, 1:52 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
unless i'm missing something, i'm guessing this code always modifies the same
sheet, rev?

so try this:

Sub base()
'
' base Macro
' Macro recorded 4/16/2007 by User
'

With Worksheets("rev")
.Range("B83").Value = "415"
.Range("B84").Value = "235"
.Range("B85").Value = "50"
.Range("B86").Value = "25"
.Range("B87").Value = "75"
.Range("B88").Value = "70"
.Range("B89").Value = "98"
.Range("B90").Value = "126"
.Range("A90").Value = "Occupancy-90% -3RD "
.Range("A89").Value = "Occupancy-70% -2ND"
.Range("A88").Value = "Occupancy-50% -1ST 4 MO."
End With
End Sub

--

Gary

wrote in message

ps.com...

yes, that is correct. I have the combo box always present, so that
even if I change tabs, I have the option of choosing a scenario.
Currently, when I pick a scenario, the macro is executed on the
current worksheet. This is no good, as it overwrites things that are
there. So I need the macro to change only the cells in the rev
worksheet. I'm thinking I need to change the lines inside the macro
to have the rev title in front of the cell, but I'm not sure how to do
this. Here's my code for the rev page

Private Sub ComboBox1_Change()
Select Case Me.ComboBox1.Value
Case "basic": Call base
Case "increased": Call Increased
Case "decreased": Call Reduced
Case "superior": Call Superior
Case "inferior": Call Inferior
End Select
End Sub

Private Sub Worksheet_Activate()
cboScenario.Show vbModeless
End Sub

-----------------

here is my code for the macro called basic (which is an option in the
combo box)

Sub base()
'
' base Macro
' Macro recorded 4/16/2007 by User
'

'
Range("B83").Select
ActiveCell.FormulaR1C1 = "415"
Range("B84").Select
ActiveCell.FormulaR1C1 = "235"
Range("B85").Select
ActiveCell.FormulaR1C1 = "50"
Range("B86").Select
ActiveCell.FormulaR1C1 = "25"
Range("B87").Select
ActiveCell.FormulaR1C1 = "75"
Range("B88").Select
ActiveCell.FormulaR1C1 = "70"
Range("B89").Select
ActiveCell.FormulaR1C1 = "98"
Range("B90").Select
ActiveCell.FormulaR1C1 = "126"
Range("A90").Select
ActiveCell.FormulaR1C1 = "Occupancy-90% -3RD "
Range("A89").Select
ActiveCell.FormulaR1C1 = "Occupancy-70% -2ND"
Range("A88").Select
ActiveCell.FormulaR1C1 = "Occupancy-50% -1ST 4 MO."

End Sub

Thank you so much! This is EXACTLY what I needed! I can't thank you
enough!

Hi,

For example, I'm in Profits worksheet and choose best scenario from
the combo box, and it changes B92 in Rev tab. Also, is there a way to
keep focus on the worksheet and have it still execute the macro?

I assume your macro was recorded and uses things like :

Range("A1").DoSomething

If you want to do this on just one worksheet, surround your entire code
(inside a sub) with:

With Worksheets("YourSheet")
'Your code
End With

Now for each "Range" make sure you prepend it with a dot:

.Range(.......

This way your code now talks to ranges on "YourSheet".

To make sure that worksheet is visible, add:

.Activate

anywhere within the with...end with construction.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com