Run a Macro when you click on a cell

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'

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.

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?

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.

[VBA]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[/VBA]

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.

Did you put it in the module for sheet that want it to work on?

example attached.

yes i went to sheet1 and right clicked and view code

Post your workbook, it will ease matters.

I am just testing out some ideas. I hope this will have all my stuff i did in the module thing with it

There is no userform or calendar control in the workbook you posted.


Try it now


Column A

THIS IS weird nothing happens when I click anywhere.

Click in column A

i closed everything i had open and ran the one you sent me. thank you so much for your help!!!!!

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.

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......

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>?

export the userform out of personal and then import it into your workbook....

Then you can delete it from your personal.xls

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.

=IF(B2<>"",(WORKDAY(B2,8)),"")

In row 2 and drag down

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.

HERE IS THE CORECT FILE. SORRY.

Quote:

---

Originally Posted by DILJEEV

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...

[VBA]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
[/VBA]

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

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.

[VBA]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
[/VBA]

You should move the application.Enableevents = true to below the last end if....I got in a hurry.