Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

trying to get value from cell -1 from target.column

excel 2010 vba

I have an event:
xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

where my "Target"
is being passed into the procedure.

What I need:

I determine the value of the Target.Column value passing a variable
mRangeColumn (this is the Column Number example "6")

Once I determine what value " Target.Value" is...
I want to get the value to the left of Target.Value
Dim strNameBox As Range
Dim strColumnBox As Integer

        On Error Resume Next
        If Target.Column = "" Then
            If Target.Column = mRangeColumn Then
             strColumnBox = Target.Column - 1 
 strNameBox.Column = strColumnBox <------ error here
"wrong number of arguments or invalid property assignment"

               UserForm2.TextBox1.Value = Target.Value
               UserForm2.ComboBox1.Value = strNameBox.Value
                ' code
            End If
            ' code
        End If

Open in new window

  • 2
1 Solution

 Since strNameBox is a range ( I suppose you want only a cell you have to give the row as well)

Set strNameBox = Range(Cells(1, strColumnBox))

Open in new window

FordraidersAuthor Commented:
ok Changed:
Dim strNameBox As String

 strNameBox = Target.Offset(0, -1)

This will do it...
FordraidersAuthor Commented:
Thanks, Got me on the right track anyway.
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now