Selection Change VBA

[ashani]

Hi
I'm using the below formula for selection change in VBA - it works fine but everytime I click on Column A & B than it comes up with error message - Runtime error 1004. Highlights this line : Dim cel As Range: Set cel = Target.Offset(, -2)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge > 1 Then Exit Sub
    Dim cel As Range: Set cel = Target.Offset(, -2)
    If Not Intersect(Target, Range("U7:U37")) Is Nothing Then
        If cel = "" Then
            cel.Select
            MsgBox "test"
        End If
    End If

Application.CutCopyMode = False

End Sub

Please can someone guide me.

Thank you

 

[Michael M]

If you click on a cell in Col "A", then this statement will be invalid as you can't access 2 columns to the left of "A"

Set cel = Target.Offset(, -2)

Try using

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Columns("A:B")) Is Nothing Then Exit Sub
    Dim cel As Range: Set cel = Target.Offset(, -2)
    If Not Intersect(Target, Range("U7:U37")) Is Nothing Then
        If cel = "" Then
            cel.Select
            MsgBox "test"
        End If
    End If

Application.CutCopyMode = False

End Sub

 

[ashani]

absolute legend - thank you so much for your help.

Can I ask you another question regarding VBA - I'm using the below coding and it's working fine but I want that everytime I click on the button than it goes to the first blank cell in Column A. Please can you guide me on that.

Sub Tabshape()
With Sheet7

.Shapes("sheet1On").Visible = msoCTrue
.Shapes("sheet1Off").Visible = msoFalse

Sheet7.Activate

Thank you

 

[Michael M]

What is in the rest of the code ?
Where did you want the cursor to go to ?

 

[ashani]

Rest of the code is
End with
End sub

I want cursor to go the sheet 1 column A in first blank cell. Thanks

 

[Michael M]

Try

Sub Tabshape()
Dim lr As Long
With Sheet7
.Shapes("sheet1On").Visible = msoCTrue
.Shapes("sheet1Off").Visible = msoFalse
End With
lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Sheet1").Activate
Range("A" & lr).Select
End Sub

 

[ashani]

Hi Michael,

thank you for the reply.

Unfortunately, it's not working for me, as its select the last filled cell of A55 instead of first blank cell of A7. The cells have data validation list there but nothing select and it's blank.

Thank you.

 

[Peter_SSs]

If Not Intersect(Target, Columns("A:B")) Is Nothing Then Exit Sub

FWIW, the simpler line below should also work for that.

If Target.Column < 3 Then Exit Sub

 

[ashani]

Thanks you Peter

What's your thoughts on my Post 6 query ?

 

[Peter_SSs]

What's your thoughts on my Post 6 query ?

Post 3?

Untested, but try

Dim FirstBlank As Range

On Error Resume Next
Set FirstBlank = Sheets("Sheet1").Columns("A").SpecialCells(xlBlanks).Cells(1)
On Error GoTo 0
If FirstBlank Is Nothing Then Set FirstBlank = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
Application.Goto Reference:=FirstBlank, Scroll:=True