Excel VBA finding / recording user selection

Question

I have encountered an error that is mildly problematic. Tho Goal of the code below is to allow the user to select the sheets (HR1 - HR20) they would like to modify, then select the cells (range of cells) on the original sheet that they want to copy to the selected sheets.

The problem is that the user has to select the range they want to change , then run the macro ( via button), select the sheets, then RESELCET THE RANGE. If they only have a single cell selected prior to the macro being run, the macro will copy the value of that single cell even after they have highlighted the range ,when prompted by the macro, they want changed. Is there some way to tell the macro only use the selected range?

Dim WSN As Worksheet Set WSN = ActiveSheet Dim sheetname As String sheetname = ActiveSheet.Name Unload Me On Error GoTo Cancel Dim rng As Range Dim myString As String Set rng = Application.InputBox("select cell range with changes", "Cells to be copied", Default:="Select Cell Range", Type:=8) Application.ScreenUpdating = False myString = Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False) 'MsgBox Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False) Range(myString).Copy If CheckBoxALL.Value = True Then Sheets("Cost").Select For i = 1 To 20 ActiveSheet.Next.Select rngS = rng.Address Range(rngS).Select ActiveSheet.Paste ActiveWindow.ScrollRow = 85 Next i End If If CheckBoxHR1.Value = True Then Sheets("Cost").Next.Select rngS = rng.Address Range(rngS).Select ActiveSheet.Paste ActiveWindow.ScrollRow = 85 End If

the steps should go make changes to sheet1, press macro button, select sheets via userform, select sheets, click apply then select area that need to be copied. MY issue was that if I just left a random cell selected prior to the macro button press, the macro would grab the data from the random cell not the range highlighted by the user. — Alberto Brown
– Alberto Brown, Commented Feb 29, 2016 at 21:05

user3598756 · Accepted Answer · 2016-02-26 15:41:24Z

didn't test it but i think that you could use 'rng' variable directly

I mean

instead of

 Set rng = Application.InputBox("select cell range with changes", "Cells to be copied", Default:="Select Cell Range", Type:=8) Application.ScreenUpdating = False myString = Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False) 'MsgBox Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False) Range(myString).Copy

you could go this way

 Set rng = Application.InputBox("select cell range with changes", "Cells to be copied", Default:="Select Cell Range", Type:=8) Application.ScreenUpdating = False rng.Copy

Good news that worked perfectly. I am still unsure why it was using whatever cell I had active before I ran the macro, but I guess that is irrelevant now.

Collectives™ on Stack Overflow

Excel VBA finding / recording user selection

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related