I would like to make the selection-based macro in my Excel workbook.
I have the macro almost ready in my module, but I need it selection-based.
I found some hints here:
How to use the Target parameter in a standard module?
and tried to combine with my previous situation:
define name on the cell in the same row, where the other cell is selected
which results in the Code as follows:
Sub Pre_Surveyform()
Dim wkb As Workbook
Dim Lr As Long
Dim n As Long
Dim Saverng As Range
Lr = Cells(Rows.Count, "B").End(xlUp).Row
Dim cstws As Worksheet, cpws As Worksheet
Dim fwsk As Worksheet, cnws As Worksheet, orwsk As Worksheet, hosws As Worksheet
Dim shp As Shape
Dim Target As Range
Dim SelectedRow As Integer
Set Target = ActiveCell
SelectedRow = Target.Row
Set cstws = ThisWorkbook.Sheets("CST Tracker")
Set cpws = ThisWorkbook.Sheets("Control Page")
Set shp = ThisWorkbook.Sheets("Control Page").Shapes("Se")
Set Saverng = cstws.Range("K" & SelectedRow).Value
MsgBox Target.Address & vbNewLine & Target.Row & vbNewLine & Target.Column & vbNewLine & Saverng
End Sub
I cannot even test this approach, as at the line:
Set Saverng = cstws.Range("K" & SelectedRow).Value
throws the error:
Object required
What object should be applied here then? I spread my hands helplessly, as both ActiveCell as well as value without set weren't successful either.
