I'm trying to write a code to say, pop a calendar if clicked on anyone of the following cells:
Sheet : "Friday"
Cell Range: D3 to D999, F3 to F999, H3 to H999
I'm hoping this function will not affect me from using the Sort (by date) later on.
Is there a way to make a cell that when you click it a calendar pops-up and you can click a date on that calendar to auto-fill in that cell?
View 9 Replies View RelatedI have read through numerous posts, but cannot get one thing to work. I am using Excel 2003, and am trying to get a calendar to pop up, to select a date, and then return that date to Cell B2.
I was successful at making the calendar pop up by the following methods:
1. Added a button to the toolbar, and assigned the Calendar Macro to it.
2. Using VB, added a sub-menu item to the right-click (context) menu, using the code below.
Private Sub Workbook_Open()
Application.OnKey "+^{C}", "Module1.Macro2"
End Sub
However, my goal is to make the calendar appear when I click on cell B2.
I tried using the following code, but when I click on B2 nothing happens.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$2" Then UserForm1.Show
End Sub
I also tried:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
With Worksheets(ActiveCell.Worksheet.Name)
If ActiveCell.Column = 2 Then
If ActiveCell.Row = 1 Then
UserForm1.Show
End If
End If
End With
End Sub
I need to popup a calendar when user clicks on a cell.
I am using the internal add-in MScal 8.0 but can only insert date
my clicking on a cell and activating the macro.
also is there a way if user selects enable macros on digital certificates
that the addin can be selected automaticly for the user?
I've been reading through the forums, but do not know how to double click to get the calendar pop up,only in a certain range (e.g. Cells G5:G85).
I already have the module: frmCalendar
Running Office 2003
I am currently using a pop up calendar in Excel 2010 that with CTL+SHIFT+B that a calendar pops up, you select your date, and then the calendar closes. What I now need is to write a code or formula or something that when I click in any cell in column F lets say that the calendar opens automatically in that cell and then still close automatically after the date selection is made.
View 3 Replies View RelatedI need to add a calendar in multiple cells. I have followed the following steps: Developer, Insert, More Controls, Microsoft Date and Time Picker Control 6.0 (SP4) and dragged the box wherever in the sheet.
However I need this selection to be reapeated for an entire row. ie I need the user to be able to select a date from a calendar for all the cells with the same column (Cloumn A).
I want to be able to show a calendar control, have the user select a date, and then fill a cell with that date. The range I would want the calendar to appear is B2:B100.
View 9 Replies View RelatedI have designed a live calendar and am trying to highlight selected dates from a column in the calendar. I am able to select single cell in the column and highlight the date in the calendar, But am unable to select multiple dates in the column and highlight multiple dates in the calendar using conditional formatting.
Eg: calender dates are from B5:H9 and multiple dates are in column J10:J13
J column
--------
J10 7/10/2013
J11 7/20/2013
j14 7/29/2013
Attached is what I use as a Calendar Macro and it works great for single active cell use.
I have a column for 'Notes' and I can only use 1 cell per item so every time I have an update about a specific item I go the to notes section and put a date and leave few spaces then type in whatever the notes are. Currently I'm doing it manually and I tried to use my macro on the Notes column and what it does is to remove everything in the cell and replace it with whatever date I choose in the calendar.
find a way to use my current Calendar Macro to add a date in the cell instead replacing everything in the cell with a date.
I must be hitting a key by accident sometimes which makes Excel highlight the cells immediately below the ones I want as well. So, if I click in the middle of cell A1, it also highlights A2, A3 and sometimes A4 even though I haven't dragged down over them.
Why is this? I can't work out how to stop it doing it. Some previously recommended hitting F8 but this doesn't seem to stop it.
I would like for a vba programmer to tell me if the following program can be written before I attempt to code it. When I open Excel I want to see cells containing names in a square array with red backgrounds. As I click on each cell I want the cell to turn green and I want an alphabetized column of the names to have a 1 recorded(after I click) indicating that the person is present. If a person's name is red then I need a 0 recorded. If possible, I would like to be able move over one column each day that I take attendance by clicking on a cell in a row above the column of names. I am a math professor and I write computer code most every day but I have only written some small routines in vba that did not involve events.
View 3 Replies View RelatedI wonder if it is possible to make a macro that right clicks a cell and then chooses a option from the list?
View 11 Replies View RelatedI'm trying to double-click a cell to edit the contents, and I am off a few pixels, it treats the double-click as a double-click on the cell border, not the cell contents, and the focus jumps to another cell in the direction of the border instead of going into edit mode. The effect is as if I had pressed Ctrl and the arrow in the direction of the border I clicked on.
The only way I've found to disable this is to turn off the option "Enable fill handle and cell drag-and-drop", but unchecking that also disables actually useful things, like dragging the corner of a formula call to copy it down, etc.
Is there a good way to disable this cell jumping when I accidentally double-click a cell border?
i want the user to be able to change the cell color on the click of a command button. the worksheet is protected. when the user click the command button the active cell changes to red and offsets by 1. then the work sheet is locked again.
The two problems I am having is 1. I want the range to begin from row 10, column k to column FD. all cells down
the second problem is the current code allows me to edit locked cell columns A to J ...
I currently have a worksheet that when a user clicks on the cell, a calendar form opens. From here they can select a date, click ok, and the date gets placed in the corresponding cell they clicked on.
Now if the user needs to change that date, i want them to be able to click on the cell again, and when the calendar opens, the date in the cell would be selected on the calendar. I was able to do this in a userform, but im not sure how to edit it to work on the worksheet. I am using the below to populate the calendar.
Code:
private sub worksheet_selectionchange(byval target as range)
if not intersect(target,range("aa16:aa24")) is nothing then .show calendar
When the user clicks the date, the below code places it in the cell.
Code:
private sub ok_click()
with active cell
.value=calendar1.value
end with
unload me
end sub
Now the below code was used when I was using a userform, but im not sure how to edit this to pull the date from the active cell.
Code:
privatesub userform_activate()
me.calendar1=date
if not tb is nothing then
if isdate(tb.value)then me.calendar1.value = tb.value
end if
end sub
We have a workbook that was used to book desks on the floor we had in our office. The work book has a calendar that was used to book desks in advance.
Recently we acquired two other floors and have several more desks available, so we are looking at adding two more userforns to the workbook to book desks on the other two floors.
The code used within the calendar to select the userform to book the desks is the following:
[Code].....
How could/would this code be adapted now that there are two other userforms for "firstfloor" and "secondfloor" using the above code the calendar only works with the groundfloor userform.
If possible can the code be adapted so teh calendar works with all 3 userforms for the groundfllor, firstfloor and secondfloor ?
I have a program where I can update the calibration due date of an item. I have attached a cut down version of my program showing the relevant areas. There is usually password protection on the worksheet so it can only be edited via the form (the vba coding removes the password protection before editing, then re-enables the password protection after editing). The "Update Calibration" button is usually on a "Menu" sheet.
Once the form is opened a serial number is typed in the textbox. The calendar button is then clicked, which brings up another form with the calendar on. The due date is selected on the calendar. When "OK" is clicked, the date label caption is then changed to the selected calendar date. When "Submit" is clicked, the spreadsheet will search for the Serial Number, once found, the label caption (being the date selected) will be entered into the cell to the right of the serial.
If the day selected on the calendar is greater than 12 the date is entered correctly onto the sheet. example: calendar date selected = 15/01/2010. shown on sheet as 15/01/2010. However, if the day selected on the calendar is 12 or less, the date is for some reason entered incorrectly onto the sheet. example: calendar date selected = 08/12/2010. shown on sheet as 12/08/2010???? What is going on here? how come the day and month are swapped around if the day is less than 12????
Using Excel 2007 Calendar Control:
I want to select a date of choice from a calendar in a field within an Excel spreadsheet.
I do not know how to do userforms and so that is why I wonder if I can add this in just a spreadsheet.
This is how far I have gotten so far: After doing the following, I have a static calendar setting in my spreadsheet like a text box showing the current date.
Developer
Insert
More Controls
Calendar Control 12.0
Basically, I run a DJ Management company, arranging bookings for 6 artists. What I'm trying to do, is have data be auto populated from the Master sheet, into separate sheets. The aim, is to create 1 master with ALL the dates for the month in, and the each of the separate sheets to only populate data relating to that artist.
For example!
Artist
Event
Location
Fee
Date
Tony
Tomorrowland
Boom, Belgium
5,000
01/05
[code]....
Now, I need to send the artists their individual schedules for the month, but I do not want to copy and paste individual rows to their new sheets as this is a pain. I know i can sort by artist and copy it easier, but really i just want to put in the info once and not multiple times.
So the master is sheet 1, on sheets 2,3,4,5,6,7 (each one assigned to a particular artist) i need the artists gigs for that month.
how to multiple dates can be selected using the calendar control object? I haven't actually explored this for very long
View 5 Replies View RelatedHave a slick way to have multiple textboxes on a userform updatable by a single calendar control located on it's own userform.
basically, i have a userform with multiple textboxes. A double click brings up another userform with a calendar control. How do you idenitfy which textbox called up the calendar?
I have a worksheet that has about 20 columns and 60 rows, but only need the data from 5 of the columns to link with outlook calendar. I want to be able to enter the date of completion of a task in the excel spreadsheet and have an reminder entered into outlook calendar a year later. I would like the Subject of the appointment to be the EMPLOYEE NAME and the Location of the appointment to be the TASK (what the employee has to renew) [these would be the column headers]. The duration can be ALL DAY for all appointments. A reminder would be fantastic!
The only other fear I have is each time I run the macro/VBA it will recreate duplicate appointments.
And I don't know if this is possible but one of the tasks (column) for renewal, ie: drug test, calls for an employee to be randomly selected every quarter. This will cause the employee to have a new annual renewal date in outlook but will outlook still retain the original annual renewal appointment date? I am sure we can live with this, but just a thought if there is a possibility of removing the original appointment it would be amazing.
I have attached example of worksheet. The yellow highlighted column headers contain the information I need renewal appointments created for.
I would like to have 4 calendar controls in a worksheet, but even with only two controls I am receiving a Compile Error - Ambiguous name detected: Worksheet_BeforeDoubleClick
View 7 Replies View RelatedLocking text in cells but not the ability to change colour of cells
******** width="234" height="60" frameborder="0" marginwidth="0"
marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" id="aswift_0" name="aswift_0" style="left: 0px; position: absolute; top: 0px;">*********>
I have a spreadsheet where I can change the colour of a cell by clicking the mouse, I also have text in many of the cells.
What I need to do is protect (lock) the text so that no one can change the text in any of the cells, but I still want to be able to change the colour of the cells by clicking the mouse in that cell.
I am using a great pop up calendar without ActiveX made by a guy called Phil Johnson. When you click a specified cell the calendar appears. The only problem is that the calendar doesn't default to today's date when it opens.
I have the original file Calendar source code if anyone needs it.
I have a spreadsheet in which there is a calendar. On this calendar are cells for the days. In these cells for the days we use colors for Vacation (Purple), Holiday (Pink), Unpaid Absences(Green) and Late days(Yellow). At the top of the page I want a cell that tells me the number of Purples are in a range (B10:X66) and how many pinks, greens and yellows there are. Is there a way to use the 'Count' to count colors in a range?
View 14 Replies View RelatedIs it possible to use one (1) calendar control to add different dates to different cells?
View 5 Replies View RelatedI have 2 comboboxes created on a userform.
This is my code for both of them
User selects a date from a pop up calendar. The date and user ID is entered into the active cell of the active sheet. This I have. I would like at the same time to have it enter the value of just the date (without the user ID) to cell A1 on the sheet labeled final.
Also, while I'm on the topic, I have another workbook where I would like to do the exact same thing with the exception that the user will only be allowed to select a date equal to today or up to 60 days from today?