insert date

[Guest]

Oh. . . o.K. I can do that, now I want to have the same code in each
worksheet of the workbook,correct? i.e. I already have the date code you
provided in there and have tried eliminating the end sub between them, seems
it does not want to work this way.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Macro will add Validation to a Dropdown list to any blank selected cell in
Column E
ActiveCell.Select
If ActiveCell.Column = 4 Then 'Limits macro action to column D
If ActiveCell.Value = "" Then 'Check to see if Target cell empty
ActiveCell.Select
With Selection.Validation
.Delete
.Add Type:=xl ValidateList, AlertStyle:=xlValidAlertStop,
Operator:= xl Between , Formula1:="=$E$1:$E$50" 'E1:E50 is location of
the list
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Else
End If
Else
End If
End Sub

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Automatically inserts today's date in cell in column C when selected
'if the cell was empty. Does not overwrite occupied cell.
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
End If
Else
End If
End Sub

I have been looking to learn more about this and have found some info about
this and found some info in "CONTEXTURES" web sight dealing with dynamic
lists that will update whenever something is added. Ilike that feature and am
also trying to figure this one out. I'll get eventually!

 

[Guest]

I'm on my way out right now, will have to pick any follow on up from homw
later......for now, try not only eliminating the ENDSUB between the two, but
also the sencond leading line of

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If that don't work, I'll have to look at it later.........
Quitting time in St. Petersburg, Florida........

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Ok Larry...........I checked it out a little more here at home..........the
only difference is that unless you want to have a different List on each
sheet, then you have to give the List a RangeName on one sheet.........I
used "MyList" so the code works with that but yu can change it if you want,
or use separate lists on each sheet, according to your
needs...........here's the code for both of the macros to work
together.........must be put in worksheet module of each sheet you want them
to work in.........

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Macro will add Validation to a Dropdown list to any blank selected cell in
'column D
ActiveCell.Select
If ActiveCell.Column = 4 Then 'Limits macro action to column D
If ActiveCell.Value = "" Then 'Check to see if Target cell empty
ActiveCell.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=mylist" '$J$1:$J$5" 'J1:J5 is location of
the list
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Else
End If
Else
End If
'Automatically inserts today's date in cell in column C when selected
'if the cell was empty. Does not overwrite occupied cell.
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
End If
Else
End If
End Sub

hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Chuck, yer a prince. I hope your wekend went well. Best to you and your, larry

 

[CLR]

Glad to hear you got it working Larry. Yup, my weekend is going
good............I got the whole week off (without pay). I'm 67 and just
moved 11,200 pounds of marble chips by hand, filling in our flower
beds........really looks nifty, but I can hardly move now <g>

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

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("NameList"),
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

 

[Guest]

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

 

[Guest]

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]

Hi Larry.........
Well, this has gone beyond the point where I can understand it in my old
head without actualy seeing code or seeing it work...........if you want,
you can send a copy to my home addy with an explanation of what you are
supposed to see and where..........or whatever, and I will take a
look.........
My home addy is croberts at tampabay period rr period com,
making the obvious replacements.

Vaya con Dios,
Chuck, CABGx3