Thread: insert date
View Single Post
  #28   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Larry Larry is offline
external usenet poster
 
Posts: 159
Default My newest setup

thanks Chuck,
I thought I had tried that but it is working pretty good now. I am getting
close to what I am after.
There are two issues I am searching to solve:

1: The first list(column A) on the Lists worksheet can contain any number
of items but the input worksheet (column C) where descrepencies are
annotated, will only go to row 17 and then no drop down will appear.
I tried to create a combo box and set the rows but this did not change the
limitation I am having.

2: The second list (column C) on the listss page is static and has a
rangename "Initials" ,it is finite and has a width of 8.43. On the input
sheet this drop down is o.k. except for the format, the column width is
pretty wide and I have not found the way to control this demension.
As Always, I appreciate your help and knowledge greatly. Larry

"CLR" wrote:

Hi Larry..........

Just add my code as a complete separate macro below the other one you have
for Sheet4..........just copy what is below here and paste it in place below
the other macro after it's "END SUB"......

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Column = 3 Then 'Limits macro action to column C
If ActiveCell.Value = "" Then 'Check to see if Target cell empty
Selection.Value = Date 'Insert today's date in Target cell
Else
End If
Else
End If
End Sub


hth
Vaya con Dios,
Chuck, CABGx3



"Larry" wrote:

Hi Chuck,
I found this handy bit of code IN Contextures samples. I managed to tweat it
to do what I have been after.

IN MODULE 1 I HAVE:

Option Explicit

Sub Workbook_Open()
Columns("B:B").Select
Range("B3").Activate
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="WAIVER%20NO.xls", _
TextToDisplay:=""
End If
Else
End If
End Sub

NO SHEET 1OR2

IN SHEET 3 (LISTS)I HAVE:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

IN SHEET 4(86x36236; an auto number)
I HAVE:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Na meList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
End Sub

THIS WORKS GREAT IN AUTO UPDATING MY LIST BUT I CANNOT GET THE DATE ENTRY
CODE TO PLAY WITH THE OTHER CODE. I AM ASSUMING I NEED TO HAVE IT IN SHEET
MODULE 4 WHERE ALL THE WORK IS BEING DONE?
I WANT TO ADD THE FOLLOWING CODE YOU PROVIDED; HELP??

If ActiveCell.Column = 3 Then 'Limits macro action to column C
If ActiveCell.Value = "" Then 'Check to see if Target cell empty
Selection.Value = Date 'Insert today's date in Target cell
Else
End If
Else
End If
End Sub