Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice q: allow user to select worksheet in middle of macro
This should be easy but I am stuck and would welcome some help or
clues. I have a macro which does various processes. In the middle of the macro , I need to pause, ask the user to select a particular worksheet from a list of 20. The macro continues, pasting a range to the selected worksheet. At present I simply do this using a userform and the user types in the worksheet name from the list of 20 but this is prone to user typing error. I would prefer a drop down list, ideally showing only the remaining choices (so that after 18 iterations, there are only the last two sheet names left). Here is what I have at present: Worksheets("Index").Activate SelectAnswer = InputBox("Type in - carefully! the worksheet name from this list.") On Error Resume Next Worksheets(SelectAnswer).Activate Range("A1").Select ActiveSheet.Paste |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice q: allow user to select worksheet in middle of macro
Hi canary2211.
In Excel 2003 I have first made an userform with a combobox and a commandbutton The form is named frmCanary2211 The combobox is named "cboSheets" The commandbutton is named "cmdProces" In the form this code ' Start on code Option Explicit Private Sub cmdProces_Click() Dim strName As String Dim intIndex As Integer strName = cboSheets.Text intIndex = cboSheets.ListIndex Worksheets(strName).Activate Range("A1").Select ActiveSheet.Paste If cboSheets.ListCount 1 Then cboSheets.RemoveItem intIndex cboSheets.ListIndex = 0 Else MsgBox "Al sheets done" Me.Hide End If End Sub Private Sub UserForm_Activate() Dim shtLoop As Worksheet Dim intLoop As Integer intLoop = 0 For Each shtLoop In ThisWorkbook.Worksheets If shtLoop.Name < "Index" Then intLoop = intLoop + 1 Me.cboSheets.AddItem shtLoop.Name End If Next End Sub ' end of code Second I created a small starter macro: Sub canary2211() Selection.Copy frmCabary2211.Show End Sub To use it, just select the cells to be copied and hit [Alt][F8]. select canary2211 and the form will be shown. HTH, Wouter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice q: allow user to select worksheet in middle of macro
Have you thought about adding a combo box to your user form and
pre-populating that with the worksheet names? -- HTH, Barb Reinhardt "canary2211" wrote: This should be easy but I am stuck and would welcome some help or clues. I have a macro which does various processes. In the middle of the macro , I need to pause, ask the user to select a particular worksheet from a list of 20. The macro continues, pasting a range to the selected worksheet. At present I simply do this using a userform and the user types in the worksheet name from the list of 20 but this is prone to user typing error. I would prefer a drop down list, ideally showing only the remaining choices (so that after 18 iterations, there are only the last two sheet names left). Here is what I have at present: Worksheets("Index").Activate SelectAnswer = InputBox("Type in - carefully! the worksheet name from this list.") On Error Resume Next Worksheets(SelectAnswer).Activate Range("A1").Select ActiveSheet.Paste . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice q: allow user to select worksheet in middle of macro
Barb, Wouter
Thanks for this - both methods work nicely. Now I just need to work out how to eliminate the sheets (or perhaps grey out the options) that are dealt with so that usr can only select from the remaining sheets Nick On Mar 12, 5:39*pm, Barb Reinhardt wrote: Have you thought about adding a combo box to your user form and pre-populating that with the worksheet names? -- HTH, Barb Reinhardt "canary2211" wrote: This should be easy but I am stuck and would welcome some help or clues. I have a macro which does various processes. In the middle of the macro , I need to pause, ask the user to select a particular worksheet from a list of 20. The macro continues, pasting a range to the selected worksheet. At present I simply do this using a userform and the user types in the worksheet name from the list of 20 but this is prone to user typing error. I would prefer a drop down list, ideally showing only the remaining choices (so that after 18 iterations, there are only the last two sheet names left). Here is what I have at present: Worksheets("Index").Activate SelectAnswer = InputBox("Type in - carefully! *the worksheet name from this list.") On Error Resume Next Worksheets(SelectAnswer).Activate Range("A1").Select ActiveSheet.Paste .- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want user to select the worksheet | Excel Programming | |||
novice excel 2007 user needs help | New Users to Excel | |||
Relative return to original worksheet in middle of macro | Excel Programming | |||
Very Novice Excel user with security question | Excel Discussion (Misc queries) | |||
Select a cell in the middle of a macro execution | Excel Programming |