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. |
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. . |
All times are GMT +1. The time now is 12:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com