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

claudehollett

Board Regular
Joined
Dec 11, 2003
Messages
89
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

Board Regular
Joined
Dec 11, 2003
Messages
89
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
 

Forum statistics

Threads
1,147,665
Messages
5,742,500
Members
423,733
Latest member
hamann666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top