Wednesday, 9 May 2018

Excel VBA finding / recording user selection



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

Answer



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

No comments:

Post a Comment

casting - Why wasn't Tobey Maguire in The Amazing Spider-Man? - Movies & TV

In the Spider-Man franchise, Tobey Maguire is an outstanding performer as a Spider-Man and also reprised his role in the sequels Spider-Man...