Make 1 UserForm macro target different predefined cells

[Arlen]

Hello, Programmers!

I created a UserForm calendar whose default behavior is to put the date you
select in the active cell.

I made a calendar icon, assigned it the OpenCalendar macro, made copies and
positioned the icons approximately in cells C4, C6, C25 and C27.

The code behind the UserForm is this:

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

I need each calendar to populate B4, B6, B25 and B27, respectively.

So I changed ActiveCell.Value to Range("B4").Value, but off course that
means all 4 icons now populate B4. I figured you could look at the cell the
icon was in and move 1 to the left, but the icon has no address. Or does it?

If someone could enlighten me on how to make each calendar put a date in its
respective B cell, I would appreciate it greatly.

I thank you for your time.

Arlen

 

[Dave Peterson]

So your macro that shows the userform (OpenCalendar macro) is assigned to each
of the 4 pictures.

If that's the case, you could do this in a General module (probably were the
OpenCalendar subroutine is:

Option Explicit
Public PictCell as range
Sub OpenCalendar()
set pictcell = activesheet.pictures(application.caller).topleftcell
CalUserForm.show 'userform1????
set pictcell = nothing
End sub

===
in the userform module.
Private Sub Calendar1_Click()
if PictCell is nothing then
'something went really bad!
'msgbox "oh, oh!"
else
pictcell.offset(0,1).Value = Calendar1.Value
end if
Unload Me
End Sub

 

[Arlen]

Dave,

It's days later and I just tried out your code. It works perfectly.

Thank you sir.

Have a great one!

Arlen