Select a range with the mouse using VBA (Excel) 2

[work4livinsean]

Hi,
I am trying to write a code that will let the user select a range with the mouse during a macro. I have found a code where the user can select the code before the macro is ran but the user may have to select many different ranges on many different sheets. I have been looking and looking and have not found any mouse events for VBA on selecting ranges. My plan is to create a loop that would first ask the user what range would he/she like to select --> the user selects the range --> the code executes --> asks the user if he/she would like select another range --> loop or end code. Maybe creating a msgbox that just says "After you have selected the range hit OK." This would work but could cause many problems. Stopping the code from executing until a range is selected would be desired. If anyone has any ideas/suggestions/codes I would greatly appreciate it. Here is the code for selecting a range before the macro is ran (I guess my code would look similar in a loop unless there is a better way to go about this):

Sub SelectRangeBefore()

' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
'Code would execute here
MsgBox "You have already selected a range."
End If

End Sub

Thanks in advance!

 

[Chance1234]

right click on the worksheet tab, and select view code

have a look at

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


Chance,

Filmmaker, taken gentleman and He tan e epi tas all change on monday

 

[work4livinsean]

Ummm...not to sure I understand what you mean. I copied and pasted the code you put there but it doesn't allow me to run it. I know I am missing a step here, could you please explain in a little more detail. Thanks.

 

[work4livinsean]

Sorry, reposting the same thing b/c I did it wrong before...
Ummm...not to sure I understand what you mean. I copied and pasted the code you put there but it doesn't allow me to run it. I know I am missing a step here, could you please explain in a little more detail. Thanks.

 

[Chance1234]

where did you paste the code ? it should be behind the sheet tab.,

and this is just the sub headings, take a look at

http://www.mvps.org/dmcritchie/excel/event.htm

Chance,

Filmmaker, taken gentleman and He tan e epi tas all change on monday

 

[Loomah]

Hi
I'd suggest creating a UserForm with a RefEdit control. Then you can show this userform every time the user needs to select a range.

The RefEdit control isn't one of the default controls in the toolbox so you'll need to add it by right clicking in the toolbox>Choos "Additional Controls...">Scroll down until you fing "RefEdit.Ctrl" (in xl97 anyway).

It works in the same way as the box that appears when using the function wizard to enter a formula in a worksheet.

You'll have to look at the help files for propeties and methods etc.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

It is also possible to use excel's Inputbox:

Dim rng As Range
Do
    On Error Resume Next
    Set rng = Application.InputBox("Message", "Title", "Please select range", , , , , 8)
    errN = Err.Number
    On Error GoTo 0
    If errN = 0 Then
        ' proceed with rng as reference to selected range
    Else
        Exit Do
    End If
Loop

combo

 

[work4livinsean]

Okay I did a combonation of the last two. I made a userform with a button titled "Range." I then made an input box that allows the user to select a range with the mouse. When selecting with the mouse the input box updates automatically (I found this code somewhere else). This is actually pretty neat so here is the code that I am using (this code comes from the userform button, I am not showing the userform here):

Private Sub CommandButtonRange_Click()

Application.ScreenUpdating = True

Dim UserRange As Range

Prompt = "Please Make Your Selection"
Title = "Select Your Range"

' Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox(Prompt:=Prompt, Title:=Title, _
Default:=ActiveCell.Address, Type:=8) 'Range selection

' Was the Input Box canceled?
If UserRange Is Nothing Then
MsgBox "Canceled."
End If

UserRange.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub


Thanks everyone for your help