Excel Calendar

[camioneur]

Hi, I've created a calendar on an Excel 2007 spreadsheet so that I can easily enter the date required in column A. The trouble is that if I accidentally click on the calendar when the active cell is other than in column A, unwanted or incorrect data is entered and because it's a financial spreadsheet with lots of formulas this unwanted cell entry data throws the whole sheet out. Is there any way I can make the calendar specific to Column A and to ignore the rest of the sheet? TIA

[aelmpvw]

highlight 'column a' and format it as calendar?

[camioneur]

Thanks but that wouldn't stop the accidental entry of data in the other columns, Column A is already date formated

[Zacs-Master]

Do you mean you've used an excel sheet and saved it as the calendar? If so, you'll need to highlight all cells and reformat them just as 'number'

[camioneur]

No I created a calendar with VBA which is superimposed onto an Excel 2007 spreadsheet used for financial stuff. (It's actually my work round for MS Money which is incompatible with Windows 10) The whole of the sheet other than column A is used for entering costs in "currency format" other than column A which is in date format. If I click on the calendar the date is automatically entered into the active cell. The trouble is that if I accidentally click on the calendar in a currency formatted cell the result is a mess and not always visible because some of the sheet is hidden with "freeze panes". My problem isn't really format related, I was looking for a VBA code that could restrict my calendar to working in Column A only and render the it ineffective in all the other columns, thanks

[camioneur]

This is the code I use at the moment:

Private Sub Calendar1_Click()
ActiveCell.Value = Me.Calendar1.Value
End Sub

[Zacs-Master]

Superimposed is the same as saved as really. You need to format all the remaining cells to 'number' other wise you'll be activating old formulae.

[pinkyboyid]

Not sure if this will work for you, but, instead of using the function 'ActiveCell.value' you could try using 'ActiveCell.Address' which will return the active Cell Address as a string format of "$A$!" i.e. if active cell is Col=B and Row=6, it will return a string value as "$B$6"

Then use the VBA 'Mid()' string function to extract the Column string value to enable a further program check to ensure it is the desired column.

i.e. Columnchk = Mid(ActiveCell.Address, 2, 1) ' extracts and returns the second character from "$B$!" = "B"

The program logic then includes a further check to make sure that the active column returned is the desired column before accepting the cell value.

i.e. assuming the desired active column is A, then revised code, based on your original, is something like:

Private Sub Calendar1_Click()
Columnchk = Mid(ActiveCell.Address, 2, 1)

' set value only if active column = A
If Columnchk = "A" then ActiveCell.Value = Me.Calendar1.Value

End Sub

[camioneur]

Pinkyboyid.. whilst I don't pretend to understand the workings of the code you suggest, I revised my code with yours and it works perfectly. It's now ignoring all cells other than in column A. I really am so grateful, thank you very much.