PDA

View Full Version : Run a Macro when you click on a cell



DILJEEV
01-20-2010, 10:56 AM
Hi everyone,

I need some help with Excel. I am trying to run a macro which is for a calendar pop up to run automatically when a cell is clicked on.

When I press Alt + F8 it shows my macro and it says that is called Personal.xls!OpenCalendar.

I need to this macro to run whenever a cell is clicked on in 'D'

lucas
01-20-2010, 10:58 AM
calendar controls will give you problems if you are sending it to other people. they may not have the calendar control installed.....

This can be done though.

DILJEEV
01-20-2010, 11:03 AM
This is for a spreadsheet that I was asked to make for my company. It won't be sent to anyone there are only a hnadful of people that are working on this spreadsheet. I created a new form and created the calendar within that and it is only linked to this workbook so I don't see that being an issue. I just need the popup to come up when I click on that column. What can I do?

lucas
01-20-2010, 11:31 AM
This is sheet change code so it needs to go in the code module for the sheet. It is set for column 1 or A

the calendar control will have to be on userform1 or you will have to change that part in the code.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
UserForm1.Show
End If
Application.EnableEvents = True
End Sub

DILJEEV
01-20-2010, 11:45 AM
I put in the code and I changed my form to Userform1 but when I click nothing happens. I changed userform1 eveywhere in my project. Nothing happens when I click anywhere. I'm really sorry for being a pest. I really appreciate your help.

lucas
01-20-2010, 11:48 AM
Did you put it in the module for sheet that want it to work on?

example attached.

DILJEEV
01-20-2010, 11:50 AM
yes i went to sheet1 and right clicked and view code

Bob Phillips
01-20-2010, 11:51 AM
Post your workbook, it will ease matters.

DILJEEV
01-20-2010, 11:55 AM
I am just testing out some ideas. I hope this will have all my stuff i did in the module thing with it

lucas
01-20-2010, 11:58 AM
There is no userform or calendar control in the workbook you posted.


Try it now


Column A

DILJEEV
01-20-2010, 12:02 PM
THIS IS weird nothing happens when I click anywhere.

lucas
01-20-2010, 12:04 PM
Click in column A

DILJEEV
01-20-2010, 12:07 PM
i closed everything i had open and ran the one you sent me. thank you so much for your help!!!!!

mbarron
01-20-2010, 12:10 PM
Sorry if I'm misreading your request. Try the attached. It should launch your OpenCalendar macro contained in your Personal.xls file when you click anywhere in the D column.

lucas
01-20-2010, 12:14 PM
I missed the personal.xls part. Thanks mbarron. You will have to add it to everyone's personal.xls that uses it. You probably should just leave it in the workbook......

DILJEEV
01-20-2010, 12:21 PM
thanks a lot guys. I think its better as well if I just include it in the workbook it self. Can I change the Personal.xls to be just in this workbok>?

lucas
01-20-2010, 12:23 PM
export the userform out of personal and then import it into your workbook....

lucas
01-20-2010, 12:26 PM
Then you can delete it from your personal.xls

DILJEEV
01-20-2010, 03:08 PM
I'M BACK AGAIN!

I have started to put the spreadsheet together. And there is a section where there is a i have to get a due date from a date submitted. I have got that fine but i have to fill down the formula however when I do this becasue the date fields it is showing a date of 1/11/1900. Is there an easy way for me to hide this until a number is populated in the corresponding field?

Attached is my spreadsheet column 2 populates column 16.

lucas
01-20-2010, 04:01 PM
=IF(B2<>"",(WORKDAY(B2,8)),"")

In row 2 and drag down

DILJEEV
01-20-2010, 04:11 PM
This puts a border around the cell?

Also, for the tracking number section. I want it to automatically go up one at a time. I know the formula to enter to do this but is there a way i can do it that the number only comes with you click on the cell?

man i appreciate all your help. If you were closer I'd take you out for some beers lol.

DILJEEV
01-20-2010, 04:19 PM
HERE IS THE CORECT FILE. SORRY.

lucas
01-20-2010, 04:25 PM
This puts a border around the cell?

Also, for the tracking number section. I want it to automatically go up one at a time. I know the formula to enter to do this but is there a way i can do it that the number only comes with you click on the cell?

man i appreciate all your help. If you were closer I'd take you out for some beers lol.

No, see attached. Also I added some code to your sheet module to autonumber when you click anywhere on the row...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Target.Column = 2 Then
UserForm1.Show
End If
If Target.Column = 15 Then
UserForm1.Show
End If
If Target.Column = 17 Then
UserForm1.Show
End If
Application.EnableEvents = True
Dim rowoffset As String
rowoffset = -1
Intersect(ActiveCell.EntireRow, Columns("A")).Value = ActiveCell.Row + rowoffset
End Sub


I'm not much of a beer drinker. I like Skyy though......one day maybe.

DILJEEV
01-20-2010, 04:33 PM
Wow you are awesome!!!! Thanks so much for all your help. Is there way I can remove that code for just one cell? The cell where it says Tracking Number? If it gets clicked there it goes to 0.

lucas
01-20-2010, 04:48 PM
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Target.Column = 2 Then
UserForm1.Show
End If
If Target.Column = 15 Then
UserForm1.Show
End If
If Target.Column = 17 Then
UserForm1.Show
End If
Application.EnableEvents = True
If Not Target.Row = 1 Then
rowoffset = -1
Intersect(ActiveCell.EntireRow, Columns("A")).Value = ActiveCell.Row + rowoffset
End If

End Sub

lucas
01-20-2010, 04:50 PM
You should move the application.Enableevents = true to below the last end if....I got in a hurry.