SetFocus to first empty cell on a column

[Diezz]

Hello,

When i open my workbook i would like it to instantly set focus on the first empty cell in the column A.

So if the cell where i last insterted data was a30 and i close my workbook, when i re-open it i would like to focus set to a31.

Also if i would try then to move my mouse to a32 and leave a31 empty, i would like it to give me an error message "no empty rows are allowed".

I can't really get any of those operations to work and everything i do seems ilogical, anyone has some ideeas?

Thanks

 

[Gavona]

I don't think you can do this without VBA/Macros?. Suggest you re-post here in the Visual Basic Forum.

Have a look at events and tell us what you have tried.


Gavin

 

[DirkStruan]

Diezz

The following works on the individual sheet's VB code (i.e. right click the sheet name tab and select view code). Copy the following in, hopefully it's what you want. It assumes that the A column is always completed first. If you delete the entries in one of the rows (say the fourth from ten), that (fourth) row will be seen as the next empty one, after that you can go to the next empty row (eleven).

Private Sub Worksheet_Activate()
Cells(1, 1).Select
Selection.End(xlDown).Offset(1, 0).Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
r = ActiveCell.Row
c = ActiveCell.Column
Application.EnableEvents = False
Cells(1, 1).CurrentRegion.Select
totrow = Selection.Rows.Count
If totrow + 1 > r Then
Cells(r, c).Select
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
End If
If totrow + 1 < r Then
Cells(totrow + 1, c).Select
MsgBox "no empty rows are allowed"
Else
Cells(r, c).Select
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Dirk

 

[firefytr]

Yes, post in forum707.

@Dirk: There's really no need to Select in your code at all.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Diezz]

Thanks very much, it's exactly what i needed

 

[DirkStruan]

Zack

I assume you mean I could have just used

totrow = Cells(1, 1).CurrentRegion.Rows.Count

and then the only Select code required would be when totrow + 1 < r (in order to drag the focus back to the valid region) as the others are referring to the preselected cell?

If so, I agree - it was sloppy.

However, I've an issue with working on non-selected ranges. Sometimes code will not work until I select the range and then work on that selection when other times it worked perfectly.

I don't use VB anywhere as much as I would like (in fact I shouldn't be really using it at all in my current job) and haven't spent the time understanding when a range selection is entirely necessary and when it isn't.

Regards

Dirk

 

[Diezz]

I've tried

Private Sub Workbook_Open()
Select Case Environ("Username")
Case 701000602
Worksheets("Ionut").Cells(4, 1).Select
Case 701000284
Worksheets("Andrei").Cells(4, 1).Select
Case 701000114
Worksheets("Carmen").Cells(4.1).Select
Case 701000119
Worksheets("Florin").Cells(4.1).Select
Case 701000116
Worksheets("Adelina").Cells(4.1).Select


End Select

End Sub

But it dosen`t seem to work if a sheet is already selected and if it has to change selection

 

[DirkStruan]

Diezz

I can't admit to knowing the reason why but I've found you either need to Activate or Select the worksheet before you can select the range/cell.

Dirk

 

[DirkStruan]

Also, for the Carmen clause, & after, you have used "." rather than "," in your cell references. Obviously a typo.

 

[Diezz]

Yes, it finally worked.

So..

Private Sub Workbook_Open()
Select Case Environ("Username")
Case 701000602
Worsheets("Ionut").Activate
Worksheets("Ionut").Cells(4, 1).Select
etc.

It works really fine, thank you all for your help