Excel VBA - setting scope of Named Range to Worksheet problem

This works, but the Named Range has a 'Workbook' scope, even though the range is qualified to ActiveSheet. I'm stuck because the macro recordser version uses RefersTo. This is simper.
How can I make it 'Worksheet' scope (using this syntax version, if possible) please?

Private Sub btnOk_Click()

    Dim strSourceRange As String
    Dim intOffsetColumns As Integer
    Dim wksActiveWorksheet As Worksheet
    Dim rngSourceRange As Range
    strSourceRange = Me.refSourceRange.Value
    Set wksActiveWorksheet = ActiveSheet
    Set rngSourceRange = wksActiveWorksheet.Range(strSourceRange)
    intOffsetColumns = Me.txtOffsetColumns
    iCol = 1
    For iRow = 1 To rngSourceRange.Rows.Count - 1

        strName = "TRAC_" + rngSourceRange.Cells(iRow, 1).Value
        rngSourceRange.Cells(iRow, 1).Offset(0, intOffsetColumns).Name = strName

    Next iRow

End Sub

Open in new window

Who is Participating?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this then...
strName = "TRAC_" & rngSourceRange.Cells(iRow, 1).Value
wksActiveWorksheet.Names.Add Name:=strName, RefersToR1C1:="=" & rngSourceRange.Cells(iRow, 1).Offset(0, intOffsetColumns).Address(ReferenceStyle:=xlR1C1)

Open in new window

Fabrice LambertFabrice LambertCommented:

Not sure to understand where you named range is referenced.

Other things I noticed:
Do you really need the activesheet ?
what if another sheet is selected ?

If yes, you'd better set your wksActiveWorksheet object to thisworkbook.worksheets(thisworkbook.activesheet.name), at least you'll obtain a stable and reliable object.
hindersalivaAuthor Commented:
Fabrice, yes I was initially trying that syntax. See here

The solution I got from there was the other syntax and it worked. But not the 'worksheet' scope I wanted.

The cope will be in an AddIn and will run on the ActiveWorkbook.ActiveSheet.
Fabrice LambertFabrice LambertCommented:
Subodh's comment might work.

I can only tell you to be extremly carefull with the ActiveWorksheet object, as it is a very evil beast.
hindersalivaAuthor Commented:
Genius! Thanks Neeraj.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.