+ Reply to Thread
Results 1 to 4 of 4

Pause a Macro to allow the user to select a cell

Hybrid View

MeBeMel Pause a Macro to allow the... 08-15-2014, 07:39 AM
TMS Re: Pause a Macro to allow... 08-15-2014, 07:45 AM
MeBeMel Re: Pause a Macro to allow... 08-15-2014, 08:02 AM
TMS Re: Pause a Macro to allow... 08-15-2014, 08:07 AM
  1. #1
    Registered User
    Join Date
    05-03-2014
    Location
    USA
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    42

    Pause a Macro to allow the user to select a cell

    I am wanting to pause a macro to allow a user to select a cell and when the user hits enter the macro starts running again. My intention is that a user would select cell B5 or B8 or B55 and then hit enter and the cell would move 6 cells right and enter a value from the macro. The following is what I have tried and even though it allows to select a cell, it doesn't make that cell the active cell.

    Formula: copy to clipboard
    Sub UserInput()
    Dim userResponce As Range

    On Error Resume Next
    Set userResponce = Application.InputBox("select a range with the mouse", Default:=Selection.Address, Type:=8)
    On Error GoTo 0
    If userResponce Is Nothing Then
    MsgBox "Cancel clicked"
    Else
    MsgBox "You selected " & userResponce.Address
    End If

    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,442

    Re: Pause a Macro to allow the user to select a cell

    Option Explicit
    
    Sub UserInput()
    
    Dim userResponse As Range
    
    On Error Resume Next
    Set userResponse = Application.InputBox("select a range with the mouse", Default:=Selection.Address, Type:=8)
    On Error GoTo 0
    
    If userResponse Is Nothing Then
        MsgBox "Cancel clicked"
    Else
        userResponse.Select
        MsgBox "You selected " & userResponse.Address
    End If
    
    End Sub

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    05-03-2014
    Location
    USA
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    42

    Re: Pause a Macro to allow the user to select a cell

    Simple enough. Thanks.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,442

    Re: Pause a Macro to allow the user to select a cell

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pause macro for user input then search for user's answer across multiple sheets
    By sassy2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-04-2009, 03:55 AM
  2. create a pause in print macro to allow user to select printer
    By Scott53 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-07-2005, 03:05 PM
  3. Macro to pause for user input in dialog box
    By kayabob in forum Excel General
    Replies: 1
    Last Post: 06-22-2005, 03:05 PM
  4. How do I pause a macro to select specific cells
    By lee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2005, 10:06 AM
  5. HELP-HOW to Pause macro until user Keystoke
    By ectoid in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-18-2005, 06:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1