Pause Macro - Select From A Drop Down - Restart Macro

  • [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]

        'ActiveCell.FormulaR1C1 = "John Smith"
       ' Application.CutCopyMode = False
       ' ActiveSheet.Paste
        'Range("c27:d27") = InputBox("Enter value")
  • 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

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

    HTH GB

    But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

  • 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.

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

    Something like this.

    Module code
    Sub MyMacro()


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

    Unload UserForm1

    End Sub

    Userform with button and combobox
    Option Explicit

    Private Sub ComboBox1_Change()
    End Sub

    Private Sub CommandButton1_Click()
    End Sub

    Private Sub UserForm_Initialize()

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

    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!