Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Can I Assign A Macro To A Cell?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I'm a novice on Excel so any help, in lamens terms, is greatly appreciated.

What I'm trying to do is create a spreadsheet where the user clicks on a cell, and a calendar pops up where they can select a date. The calendar then disappears and the date populates the cell.

I've created the macro by following the instructions on the following website - http://www.fontstuff.com/vba/vbatut07.htm which works fine. I can call up the calendar either by pressing a button or right-clicking.

I'm not sure if it's possible but what I want to happen is for the calendar to pop up when the user clicks on the cell, rather for them to have to right-click or click a button. Is this possible?

Any help is appreciated.

Regards, K-Rod.


Similar Excel Video Tutorials

Helpful Excel Macros

Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Open a Word Document from Excel
- This Microsoft Excel macro will open a Microsoft Word document from excel. With this macro, you can open any word docume
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display

Similar Topics







I'm trying to make it so that when a user clicks on a certain cell, a calendar pops up so they can pick a date. This seems like a pretty common thing to want; I hope Microsoft puts it into the next release.

Anyway, I've read the tutorial found at http://www.fontstuff.com/vba/vbatut07.htm which tells you how to create a userform, add the calendar control to it, etc.

I've also modified it so that the calendar comes up when the user clicks on a certain cell, and so that the form closes when they choose a date.

I had to use the selection_changed subroutine to tell if someone clicked on the cell, but there are some flaws.

First, moving over to the cell with the keyboard arrows brings up the calendar (undesired result; I only want it to come up with clicking)

Second, if the cell is already selected, clicking it doesn't bring up the calendar since the selection didn't change (also undesired; I would like the form to come up whether the cell was previously selected or not).

Does anyone have any suggestions? Or maybe an alternative way to go about it?

Thanks,
B.J.


When a user enters a cell (merged or not) that is formated with a date format, I want to pop up a button right next to the active date formatted cell?

Then I want this button to open a date picker/pop-up calendar right next to the button. Then when the calendar closes... so does the button.

I have created a pop-up calendar from http://www.fontstuff.com/vba/vbatut07.htm then I coded it with code from http://www.fontstuff.com/mailbag/qexcel02.htm to automatically open the calendar when specific cells were activated. It works great as long as the specific cells are not merged. However I have to merge them. Unfortunately the integrity of the spreadsheet is more important than the calculator. I also need dates all over the workbook... so this solution will actually work better.


Hello Everyone,

I'm trying to create an in cell pop-out calendar so the user can select
from a calendar the date to be inputed into a time sheet.

I create one using these steps found at this website:
http://www.fontstuff.com/vba/vbatut07.htm

For some reason I get an error when i run the macro:

Run-Time error 2147417848 (80010108)
Method "Value" of objec "ICalendar' failed

Anyone have any idea on how to solve this or another way i can go about
creating a pop-out calendar?

Thank you,

Sean




Hello -

I found this link while looking for help on how to implement a frmCalendar: http://www.fontstuff.com/vba/vbatut07.htm.

I'd like to know how to adjust this to make a calendar pop up when clicking on only cell A2. I'd like a user to double-click a date, have that date entered into A2 as "mm/dd/yy", and then have the calendar close.

Any help would be greatly appreciated!


Hello Everyone,

This is my first post here. So, before I start, may I say a big hello to everyone.

I have been trying to complete a excel spreadsheet that incorporates a VBA calendar. You will know the sort of thing that I mean, the user clicks on a cell and a calendar appears then the user clicks a date and the date is added to the active cell. The reason why I have to do this in VBA is because the finished file is going to be stored on my works server and it doesn't have the calendar activex add-in installed.

Well I have made a little progress. However, I have now become stuck. the link below will give you the file that I got so far. If you extract the file then click on cell A1 a calendar will appear. However, you will see that when you try to change the month it is stuck on January and I do not know how to cure the problem.

I should add that the code that is written within this spreadsheet was not written by me. It was originally written by another person whom I am no longer able to contact. Unfortunately my knowledge of VBA is not that of the original author.

Any help on this matter would be greatly appreciated

Thanks in advance

Paul
http://www.ellandpool.co.uk/Calendar10.zip


I am looking at inserting a calendar within my spreadsheet. Nonetheless, I
want the calendar to pop up WHEN I click within any cells within the range
H6:H900.

I have looked at sites such as http://www.fontstuff.com/vba/vbatut07.htm and
am ok with setting up the calendar. Nonetheless, how do I set it up so that
the calendar automatically pops up when I click in any cell within the range
H6:H900?

Thanks





Okay so I created a calendar and made it so when I click on a cell, it pops up and allows me to choose the date. When I choose the date and press okay, it is supposed to put that date in the cell. I created the spreadsheet a while back and remember it working. Now however, it doesn't and I can't figure out how. When I made the calendar, I followed some help posted on here and copied the VBA from their calendar since I don't really know VBA at all. Here is the worksheet. It should have the calendar already created via VBA. I just need to know how to have the popup calendar to place the date selected in E5.

Any explanation on how the solution works would be greatly appreciated too so I can try to understand the solution. Thanks!


I added the Calendar feature from the ToolBox "More Controls" - Calendar Control 8.0 to a form. I changed the control source of the calendar to a date/time field on the form, so that this field would store the date that users click on in the calendar. This works fine, but it is not "real-time". The date/time text box does not "update" each time the user click a different date on the calendar. Is there anyway to accomplish this?

Also, does the Calendar always need to be displayed? Or is there an option like in 2007 where the Calendar is "minimized" until the user clicks the square next to the text box? Thanks.


I have created the calendar user form and the macro to trigger a calendar to popup in the active cell. However, for the life of me, I cannot figure out how to have it so that when I click in a cell in a pre-set range of cells that the calendar pops up automatically.

Any help would be greatly appreciated.

Thanks,
Bob Kaplan


Hello all,

BASIC HISTORY OF WHAT I'M DOING
I have a popup calendar that appears when a user selects/activates a specific cell (e.g. B6) by single mouse click or by navigating to the cell by using the arrow keys. When the cell is selected/activated, the calendar appears with the current day already highlighted and the user can then click on whatever day they want and after they click on the day they want, the calendar closes.


PROBLEM
While creating a new macro that will clear specified populated cells, I selected cell B6 to remove the date that is in cell B6 and the calendar popped up. I closed the calendar by clicking the close button ("x") and then deleted the date out of cell B6. After I finished all of my steps, I checked the macro and everything recorded except when I closed the popup calendar.

Does anyone have any idea of how I can include code in my macro that will close the popup calendar when it appears?


I tried using the 'SendKeys' statement for Alt+F4, but this did not work.

Thank you to everyone in advance.


Hi all,

When a user double-click certain cells, a calendar pops up to allow the user to click and insert a date into the active cell. When the user clicks the Cancel button, it unloads the pop-up calendar but the active cell remains in the editing mode - the curser is still flashing.

What code can I use so that when the user double-clicks the cell it shows the pop-up calendar but does not put the cell in editing mode.

Thank you,
Gos-C


Hi All

I'm using Excel 2002 and am currently creating a holiday tracker.
I am looking to integrate a calendar button into this sheet.
For e.g. when someone clicks on the button a popup window opens up with the calendar for 2008.
I am not looking for a drop-down calendar. I simply want users to see the yearly calendar when they click on the button.

Any help would be appreciated.

Thank you.

A Bellad


I have been able to successfully use the built-in calendar function to create a drop down calendar for the user to input a date, which then populates any given cell to which I link the calendar functionality.

My question is - is there a way to link the same calendar functionality to MULTIPLE cells in a column?

Ideally, my spreadsheet will contain one column into which only dates will be entered. I would like to allow the user to click on THE SPECIFIC CELL into which they would like the date to appear; this action will initiate the pop-up drop-down calendar REGARDLESS OF THE ROW in the date column; the user will then select the desired date from the calendar, and that date will then populate the chosen cell.

Thanks in advance.


Hi,

I've created a calendar in VB following the instructions in this link - http://www.fontstuff.com/vba/vbatut07.htm

The calendar works fine but the problem I've got is I've got unwanted copies of the shortcut when I right-click. If you scroll down the URL, right at the bottom in blue is a paragraph that describes the problem and how to get rid of it. I've tried this numerous times but the duplicate entries keep re-appearing.

I've attached my workbook in the hope that any of you experts would be so kind as to take a look and offer any help or advice on how to fix this problem?

Any help is greatly appreciated.

Regards,

K-Rod.


Good morning, folks! Need some VB/macro help with this one, it's been stumping me for hours.

I created a standard vb calendar object so that someone can click a button in my form, pop open a calendar, and select a date instead of just entering one manually.

What I'd like is to have the button to activate this control be a Combo Box, so that people can click the down arrow and the calendar will pop up. However, the Combo Box doesn't seem to respond to Click events, and the Change event doesn't work either for the OpenCalendar function.

I've tried using an invisible autoshape placed over the cell where the date field is in the form and, while this does work as a button by just assigning the macro, it doesn't give focus to that cell when it's clicked, so when a date is chosen from the popup calendar it just inserts the date into whichever cell currently has focus.

I've also tried using a command button placed over the combo box but when I set the command button's Visible property to false the button simply no longer becomes clickable.

So, just to sum things up, here's what I'd like to be able to accomplish: I have a cell on a form for inserting a date using a graphical popup calendar object. I would like this cell to show a combox box / pull down menu so that when the down arrow is clicked it pops up the calendar. As always, everyone's help is greatly appreciated!


Hi ,

I am working on a file where i want users to click Cells on the Calendar in the Excel sheet(attached).

If user clicks on a cell in the Calendar Range("K7") populates the date selected as start date ,once the Range ("K7") is used.

and user selects next cell in Calendar, Range ("M7") is filled with Cell selected from the Calendar,

Help Please.

Many Thanks,

niksun

I have created a pop-up calendar using this link

http://www.fontstuff.com/vba/vbatut07.htm

My only question now is how can I link this to a range of Date/Time cells on my worksheet so that I can have a "Calendar" icon on the right beside the cell

tks


Hi all,

I have created a pop uop calendar using the following tutorial with success but midified it a little bit so the calendar pops up when i click on either A1 or A10 without the need to right click to insert the date. This is what i wanted.

My problem is:
1) When i click in cell A1 is there any way to make the calendar control always default to todays date?

2) when the date is entered in A1 and i move to A10 ideally id like the pop up calendar to open up at 30 days later to the date entered in A1.

Can anyone tell me if this is possible at all please?

Cell A1 is the date they want to begin
A10 (the reason i need the 30 day thing) is the minumum lead time they need.

Added*****by the above when the calendar appears the last date pressed is always depressed by default id like this to be the current date.

Many thanks for any help!


Hello all,
I have been working on a problem for a few days. I am trying to get a button that i have created in Excel to open the calendar that i created in VBA. Thanks to tracktor with his post : (http://www.mrexcel.com/forum/showthr...t=calendar+VBA), i was able to create this calender. However, i can't figure out how to assing a button to it.

What i'm looking for, is when the end user pushes the button, the calendar pops up. i have got the coding working where the calendar goes away, but can't figure out how to get the button to open it...

Any and all help is appricated.

Thanks Again,
Eric


Good Evening again,

As part of my big project, I was hoping that it's possible to insert or create a pop-up calendar for selecting a date. Similar to those you might see on a hotel reservation website. By clicking on the calendar icon, a larger calendar would open and one could simply click on the desired date and that date would self populate in a cell. I hope this isn't a nutty request for information. As always, thank you in advance for any help.


Hi all ,

Among all topics i didnt find what i need, what i need exactly is in a certain cell i need to insert a combo box form and when i click the arrow to drop down a small calendar from which i can click the needed date .

It is not a show calendar form button i need so when i click it it pop ups a calendar .
This didn't work http://www.rondebruin.nl/calendar.htm

I appreciate very much your help .


I created a user form, on the form I have a text box, when the user clicks into the text box a calendar appears and the user can choose the date which then shows in that text box.

This works fine at home. However I emailed the user form into work yesterday and tested it today at work and I get an error message, basically it does not work.

Now when I created the calendar I clicked on the tool bar in vb for additional tools and selected the calendar option. Is this the cause of the problem?
As this option is only set on MY PC at home and not on my work pc or for other colleagues?

I checked for the calendar option in vb at work but could not find it. At home I have excel 2003 and work it is 2000 or 2002. I assume that either earlier excel products did not have this option or it has been disabled.

Is there a way to create a calendar without having to use the additional tools options in vb,

I want the calendar to pop up when the user click in a text box ON the user form and then the Date to be entered into that text box. Is there a calendar work around?

I have been on Google for some time looking for the answer.

Cheers


Hi,

I created a userform to allow users to enter data. One of the issues is that we are relying on them to enter the date correct. So I wanted to create a way for the date to be prepoluted and defaulted to today, or they can click on it and a calendar with will pop up. They then select the date on the calendar, and that causes the calendar to dissapear and the date is the populated into the user box.

I managed to create a form with a calendar on it. On my Entryform I put the following:

Private Sub dte_Change()

Calendar.Show

End Sub

On the Calendar form, the code I tried taking from another forum and it doesn't seem to be doing anything.

Private Sub calendar_click()

Me.TextBox.Value = Me.Calendar.Value

End Sub

Thanks in advance for your help!

Thanks

Hi all, I have a calendar popup built into my spreadsheet so that when the user presses Ctrl Q it pops up a selection calendar. The problem is that I want to restrict the dates that they can enter. In other words I tried to simply add validation to the active cell so that if they chose a date outside of my specified validation range it will not accept it. Everything works fine so that the calendar pops up and they are able to select any date on it and it will populate the active cell but the validation doesnt work when you use the calendar just when you type the date in the cell.

The code for my Calendar is:

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

Is there anything I can add to this code or somewhere else that will validate whether the selected calendar date is within my range. (say April 1 2010 to June 30 2010) Again, validation on the cell doesnt seem to work when I select a date with the calendar only when typed in the active cell.

Thanks so much!


Hey guys,

I followed the instructions here to make a calendar drop-down in Excel 2002

http://en.allexperts.com/q/Excel-1059/Excel-2002-14.htm

Everything works fine, except when i click on my cell, the calendar pops up fine but when i click on a date, nothing happens... what did i do wrong??

thanks

df