changing a cell value in a different sheet from several sheets

[Ooz]

Hi there,
In my workbook I have several worksheets all containing different
reports. In the first worksheet there is a cell which has a in-cell
dropdown list as a validation criteria. This cell defines the reporting
period for all other worksheets. ie when you change this cell to say 5,
all the other worksheets become the reports for period 5.
When viewing the reports generated in other worksheets it is
inconvenient to go back to the first one and chage the reporting period
every time.
Is there a way that I can change this cell say A1 in Sheet1 from each
individual report worksheets (say from Sheet2, Sheet3, ...Sheet14,
etc.
appreciate your time.
thx



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


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[Tom Ogilvy]

What about a floating toolbar that has a dropdown in it? Would that work?
That would require that you put a macro in your workbook.

 

[Ooz]

yes, that seems to be a solution for my problem but I do not know to put
any control of mine to a toolbar. Is is possible to place controls
otherthan predifined buttons for commands and macros on to tool bars.
It would be great to have a dropdown list like the one for font size
that changes A1 in Sheet1 on a tool bar which floats over all the
worksheets.
can any one explain the procedure if such thing is possible



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


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[Tom Ogilvy]

this is some code previously posted by Rob Bovey that illustrates creating a
floating commandbar with a dropdown combobox and the macro assigned to
selection of an item in that control:

Option Explicit

Private Const szBAR_NAME As String = "ComboDemo"


Sub AddCmdBarCombo()

Dim cbrBar As CommandBar
''' Note, the class name of both types of control is CommandBarComboBox
Dim ctlComboBox As CommandBarComboBox
Dim lCount As Long

On Error Resume Next
CommandBars(szBAR_NAME).Delete
On Error GoTo 0

Set cbrBar = CommandBars.Add(szBAR_NAME, msoBarFloating)
cbrBar.Visible = True

Set ctlComboBox = cbrBar.Controls.Add(msoControlComboBox)
ctlComboBox.OnAction = ThisWorkbook.Name & "!HandleCombo"

For lCount = 1 To 5
ctlComboBox.AddItem "Item " & lCount
Next lCount

ctlComboBox.ListIndex = 0

End Sub


Sub HandleCombo()

Dim ctlComboBox As CommandBarComboBox
Dim szItem As String

Set ctlComboBox = CommandBars.ActionControl

szItem = ctlComboBox.Text

''' This is required to overcome a screen refresh bug in Excel 97.
Application.ScreenUpdating = False
Application.ScreenUpdating = True

MsgBox "You chose " & szItem

End Sub