open to first blank cell in col B, then post current date in

[claudehollett]

As the file opens, move to cell B6. If B6 not blank, move down to first blank cell in col B. After data manually entered in col B blank cell, automatically move to col A on same row and enter current date (needs to be a permanent date not just TODAY() function). Move to col E and stop.

Please help with coding. Thank You.

Claude Hollett

 

[HalfAce]

Re: open to first blank cell in col B, then post current dat

Howdy,
Because you are making the manual entry in column B first, this requires two seperate routines. The first assumes the sheet you want to open up to is named Sheet1. (Change that to suit.)

This goes into the ThisWorkbook module.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
Sheets("Sheet1").Select
<SPAN style="color:#00007F">If</SPAN> [B6] = "" <SPAN style="color:#00007F">Then</SPAN>
[B6].Select
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">ElseIf</SPAN> [B7] = "" <SPAN style="color:#00007F">Then</SPAN>
[B7].Select
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
Else: [B6].End(xlDown)(2, 1).Select
End <SPAN style="color:#00007F">If</SPAN>
End <SPAN style="color:#00007F">Sub</SPAN></FONT>


This one goes into the Sheet module for the sheet of interest.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Target.Column <> 2 Or _
   Target.Count > 1 Or _
   Target = vbNullString <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

Target.Offset(, -1).Value = <SPAN style="color:#00007F">Date</SPAN>
Target.Offset(, 3).Select
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

This will do what you've asked.
Hope it helps,
Dan

 

[claudehollett]

Re: open to first blank cell in col B, then post current dat

Dan, this worked very well. Thanks. Since this sheet has 10,000 rows, shouldn't I turn off Screen Updating in the Open module?

One more quick question before you go: I need (in col E) to offer the user a list of 5 choices for entry. Using a combo box works but I don't want to insert a combo box 10,000 times. The sheet also looks less than desireable with all those combo boxes. Is there a better way? Claude