Run a Macro when you click on a cell

[DILJEEV]

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]

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]

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]

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]

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]

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

example attached.

[DILJEEV]

yes i went to sheet1 and right clicked and view code

[Bob Phillips]

Post your workbook, it will ease matters.

[DILJEEV]

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

[lucas]

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


Try it now


Column A

[DILJEEV]

THIS IS weird nothing happens when I click anywhere.

[lucas]

Click in column A

[DILJEEV]

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

[mbarron]

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]

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]

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]

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

[lucas]

Then you can delete it from your personal.xls

[DILJEEV]

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]

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

In row 2 and drag down

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

[DILJEEV]

HERE IS THE CORECT FILE. SORRY.

[lucas]

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]

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]

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]

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