Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Return Cell References
I have a staff holiday worksheet showing months across and days (numbered 1
to 31) vertical. As the input box moves I want another cell reference to show the month name and date ie. if cursor cell ref= b21 then another cell would display February 21th. I need this to change in real time irrespective of the data held in the cell actioned. |
#2
|
|||
|
|||
With the months in B1:M1 and 1-31 in rows 2-32, right-
click the worksheet tab, select "View Code", and paste in the code below: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim CurrMonth As Long Dim CurrDay As Long Dim CurrDate As Date CurrMonth = ActiveCell.Column - 1 CurrDay = ActiveCell.Row - 1 CurrDate = DateSerial(2005, CurrMonth, CurrDay) With Me.[A1] If Month(CurrDate) < CurrMonth Then .Value = "Non-existent" Else .Value = CurrDate End If End With End Sub --- The first 2 lines are actually one line. The date should appear in A1. HTH Jason Atlnata, GA -----Original Message----- I have a staff holiday worksheet showing months across and days (numbered 1 to 31) vertical. As the input box moves I want another cell reference to show the month name and date ie. if cursor cell ref= b21 then another cell would display February 21th. I need this to change in real time irrespective of the data held in the cell actioned. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to Copy COUNTA() with cell references | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
Excel: how to formulate conditional cell references | Excel Worksheet Functions | |||
Transferring cell content between workbooks using cell references | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |