Data validation box popup when select Col A in first empty row, then delete when next cell selected

[Ironman]

Hi

I've put this code together from other bits and pieces in my workbook but it needs gluing together properly and I'm stuck!

I need the below to run only in sheet 'Training Log' when Col A in first empty row is selected:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Range("A23358").End(xlUp).Offset(0, 0).Select then ' select Col A in first empty row
With Application
        .SendKeys "i"
        .Dialogs(xlDialogDataValidation).Show  ' show data validation input box with below msg
    .InputTitle = "Reminder"
    .InputMessage = "For Indoor Bike, key Control+\ now"
End With
End If
End Sub

(I got the validation box but no msg - the Input title/msg errored 438 - Object doesn't support property or method)

Then when another cell is selected I need the validation input message clearing.

Many thanks!

 

[johnnyL]

I think your title and message would go where you set up the datavalidation

 

[Ironman]

Hi Johnny, thanks for replying - sorry, I'm not clear on that?

 

[Michael M]

I'm a little confused here.....
Why not simply have the DV in the cells down the column and select the cell...you don't need any code to do that ??

 

[Ironman]

Fair question - simply because it's irrelevant to retain DV in filled rows.

Thinking about it differently, yes it's more straightforward to do as you suggest, but I would need to erase the DV in Col A of the row above the first empty row (not all cells in Column A as some cells in column A have DV for unrelated notes) when the workbook closes.

 

[Michael M]

"but I would need to erase the DV in Col A of the row above the first empty row"

Why ??

And I would avoid sendKeys wherever possible....they will cause serious issues if the correct sheet is not active !!

 

[Ironman]

Again, because it will no longer be relevant - it's a reminder when I select the cell before data is entered in that row, which will be meaningless (and annoying) after data has been entered.

SendKeys point noted - thanks.

 

[Michael M]

It may not be relevant, but it is also not resource hungry either....unless of course you have the entire 1000000 rows of them !!

 

[Ironman]

I suppose I could just highlight and clear a load of them at a time when I remember, but I'd prefer not to have to do that if I can avoid it.

I'm guessing it's not as resource hungry as conditional formatting then.

 

[Michael M]

I'm guessing it's not as resource hungry as conditional formatting then.
I don't believe so
If you want to use a list of DV data use

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
If Intersect(target, Range("A" & lr + 1)) Is Nothing Then Exit Sub ' select Col A in first empty row
ActiveCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="YOUR LIST OF STUFF IN HERE"
cells(lr,1).validation.delete
End Sub