I have a spreadsheet that automatically updates links to 12 monthly sheets
when opened. The user then selects a month of interest in a dropdown list and
views the desired information for the month selected. My problem is this a
networked version that can take a long time to update all 12 links upon
opening. I would like to stop the auto updating, which I know how to do, and
write a macro to update only the link corresponding to the month selected by
the user. The users could manually update the link but they are not
experienced users so I am creating the macro to simplify. I know how to write
a macro to update all links but I would like the macro to only update the
link to the month selected by a user. I am not a real good code guy but is
seems like I need to set a variable somehow pulling from a range that the
selected month exists, then find a way to incorporate this into the
updatelinks routine. Thanks for your time.

If the combobox is on the sheet itself, you just need to get the name of the
sheet they selected, something like
Private Sub ComboBox1_Change()
MsgBox ComboBox1.Text
End Sub

take combobox1.text and pass it into something to update the sheet. and
depending on what exactly you are updating
Sheets(combobox1.text).Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Loopi" wrote:

I have a spreadsheet that automatically updates links to 12 monthly sheets
when opened. The user then selects a month of interest in a dropdown list and
views the desired information for the month selected. My problem is this a
networked version that can take a long time to update all 12 links upon
opening. I would like to stop the auto updating, which I know how to do, and
write a macro to update only the link corresponding to the month selected by
the user. The users could manually update the link but they are not
experienced users so I am creating the macro to simplify. I know how to write
a macro to update all links but I would like the macro to only update the
link to the month selected by a user. I am not a real good code guy but is
seems like I need to set a variable somehow pulling from a range that the
selected month exists, then find a way to incorporate this into the
updatelinks routine. Thanks for your time.

Not currently using combo boxes. The workbook is linked to 12 individual
workbooks across a network. User looks at desired information by selecting
the month of interest. Using indexing, tables and charts that are viewed are
set to display the month selected with a conditional formatting dropdown.
Currently the sheet is forced to update all 12 links upon open. This update
across the network takes a long time. I would like to let the user hit a
button after selecting the desired month and update only the one link saving
a lot of wait time. I know how to do the button thing and all of that but I
want the macro to detect what month the user has selected and update the link
to the sheet for the corresponding month

Thank you

Not currently using combo boxes. The workbook is linked to 12 individual
workbooks across a network. User looks at desired information by selecting
the month of interest. Using indexing, tables and charts that are viewed are
set to display the month selected with a conditional formatting dropdown.
Currently the sheet is forced to update all 12 links upon open. This update
across the network takes a long time. I would like to let the user hit a
button after selecting the desired month and update only the one link saving
a lot of wait time. I know how to do the button thing and all of that but I
want the macro to detect what month the user has selected and update the link
to the sheet for the corresponding month

Thank you

You're probably not going to find a better solution thatn the Combo box one
.....
--
Time is just the thing that keeps everything from happening all at once


"Loopi" wrote:

Not currently using combo boxes. The workbook is linked to 12 individual
workbooks across a network. User looks at desired information by selecting
the month of interest. Using indexing, tables and charts that are viewed are
set to display the month selected with a conditional formatting dropdown.
Currently the sheet is forced to update all 12 links upon open. This update
across the network takes a long time. I would like to let the user hit a
button after selecting the desired month and update only the one link saving
a lot of wait time. I know how to do the button thing and all of that but I
want the macro to detect what month the user has selected and update the link
to the sheet for the corresponding month

Thank you

Sorry:
Having not much of a clue about VB, my question was probably poorly worded.
Below is typical concatenated cell content that is developed when the user
selects a month, in this case January:
K:\Operations\Daily Production Sheets\2009\01-09\0109 Production
Reports.xlsb. This part is already done and works. I want to pass this
information from cell range where it exists into the statement:
(ActiveWorkbook.UpdateLink Name:= _ €œ €œ, _ Type:=xlExcelLinks)
in-between the quotes where you would typically indicate path to the desired
file.
Thank you for your time

"John Bundy" wrote:

If the combobox is on the sheet itself, you just need to get the name of the
sheet they selected, something like
Private Sub ComboBox1_Change()
MsgBox ComboBox1.Text
End Sub

take combobox1.text and pass it into something to update the sheet. and
depending on what exactly you are updating
Sheets(combobox1.text).Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Loopi" wrote:

I have a spreadsheet that automatically updates links to 12 monthly sheets
when opened. The user then selects a month of interest in a dropdown list and
views the desired information for the month selected. My problem is this a
networked version that can take a long time to update all 12 links upon
opening. I would like to stop the auto updating, which I know how to do, and
write a macro to update only the link corresponding to the month selected by
the user. The users could manually update the link but they are not
experienced users so I am creating the macro to simplify. I know how to write
a macro to update all links but I would like the macro to only update the
link to the month selected by a user. I am not a real good code guy but is
seems like I need to set a variable somehow pulling from a range that the
selected month exists, then find a way to incorporate this into the
updatelinks routine. Thanks for your time.