Ensure user selects next available Cell in Column A to avoid blank rows

[CarolineGMartin90]

Hi there!

Hoping someone can help me out! I have written up the below piece of code, which will check if the column directly above the selected Cell in Column A is empty or not, and if it is empty the macro will find the next available cell and make it the ActiveCell - thereby guiding the user to where they must input their data.

I will have that piece of code in a module which I will call, using a Private_Worksheet_SelectionChange, only when the user has selected a cell within Column A.

Both pieces of code run perfectly as Subs when I F8 through them - my problem comes when I want it to run automatically using the Private_Worksheet_SelectionChange.

My question is - does a Private_SelectionChange not recognise merely a "selection", and only a Change of value within a cell? Is there a different declaration I should be using?

Or does anyone have any other suggestions for me?

All help greatly appreciated!!

Caroline

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(ActiveCell, Range("A:A")) Is Nothing Then​

Call SelectionColumnA​

End If​

End Sub​

'to be stored in a module
Sub SelectionColumnA()

If Selection.Offset(-1, 0) = "" Then 'see if cell above is empty​

Selection.End(xlUp).Select​

ActiveCell.Offset(1, 0).Select 'select the next available cell in Column A I want user to enter data into​

End If​

End Sub
​

 

[dmt32]

Hi,
see if this does what you want

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Call SelectionColumnA(Me)
    End If


End Sub

'to be stored in a module
Sub SelectionColumnA(ByVal sh As Object)
    On Error GoTo exitsub
    Application.EnableEvents = False
    sh.Cells(sh.Cells(sh.Rows.Count, "A").End(xlUp).Row + 1, 1).Select
exitsub:
    Application.EnableEvents = True
End Sub

Dave

 

[CarolineGMartin90]

Thank you so much Dave!! Works an absolute treat!! Exactly as I wanted!

Just out of curiosity so I learn, why must it be "Call SelectionColumnA(Me)" instead of just "Call SelectionColumnA" - previously when I have called modules I have not used them. Is there a difference?

Really appreciate it!

Caroline

 

[dmt32]

Thank you so much Dave!! Works an absolute treat!! Exactly as I wanted!

Just out of curiosity so I learn, why must it be "Call SelectionColumnA(Me)" instead of just "Call SelectionColumnA" - previously when I have called modules I have not used them. Is there a difference?

Really appreciate it!

Caroline

Hi, glad suggestion helped.

As the sheet is active, there should be no real need to pass the worksheet object (Me keyword) to the procedure in standard module - its just I am old school & like to qualify code. Passing worksheet object just ensures the code is working with the intended worksheet. This is something to think about in future where you have a procedure that needs to work with more than one worksheet.

Dave

 

[CarolineGMartin90]

Ah OK - that makes sense! Never knew that - learn something new everyday

Many thanks for your help!