Pause Macro - Select From A Drop Down - Restart Macro

honoliipali · Dec 17th 2007

[COLOR="Blue"]I am trying to pause a macro on a protected sheet, select 2 adjacent cells (initially protected), utilize an existing drop down box to select a name from the list, copy the name from the list into the range of cells, then re-start the macro.
I had no problem when there was just one name (see John Smith below).
I tried to use the InputBox command but needed to actually type in the name.
Any ideas as to how to do what I need?

Thank you all.[/COLOR]

Code

ActiveSheet.Unprotect
    Range("C27:D27").Select
    'ActiveCell.FormulaR1C1 = "John Smith"
   ' Application.CutCopyMode = False
   ' ActiveSheet.Paste
    'Range("c27:d27") = InputBox("Enter value")

GrahamB · Dec 18th 2007

Re: Pause Macro - Select From A Drop Down - Restart Macro

hi honoliipali,

I have never attempted what you are asking, however, two thoughts spring to mind.

1. Use two macros, the first initiates the start, the second as a cell change macro. ie by using

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

or 2 create a userform and use one of the selection controls.

HTH GB

Dave Hawley · Dec 18th 2007

Re: Pause Macro - Select From A Drop Down - Restart Macro

A UserForm with a ComboBox on it sounds like what you need.

honoliipali · Dec 20th 2007

Re: Pause Macro - Select From A Drop Down - Restart Macro

Thank you both but I am a newbie and don't understand either suggestion. I am intrigued by the ComboBox but can't seem to find any information that addresses anything like what I want to do.

**Andy Pope** · Dec 20th 2007

Re: Pause Macro - Select From A Drop Down - Restart Macro

Something like this.

Module code
[vba]
Sub MyMacro()

UserForm1.Show

Range("A1") = UserForm1.ComboBox1.Value
Range("B1") = InputBox("Enter value")

Unload UserForm1

End Sub
[/vba]

Userform with button and combobox
[vba]
Option Explicit

Private Sub ComboBox1_Change()
Me.Hide
End Sub

Private Sub CommandButton1_Click()
Me.Hide
End Sub

Private Sub UserForm_Initialize()

' fill combobox with names
ComboBox1.List = Array("Name A", "Name B", "Name C")

End Sub
[/vba]

Pause Macro - Select From A Drop Down - Restart Macro

Participate now!

Share