Place Userform differently for each ActiveCell pressed

Question

I have a UserForm MonthView and DTPicker that will populate when certain cells are clicked. I have a form located directly below the first cell, but I would like it to be filled right below each active cell, and I tell her to activate it. My current activation code for posting a custom form:

Private Sub UserForm_Activate() With frmCalendar .Top = Application.Top + 340 .Left = Application.Left + 330 End With End Sub 

and my code for selecting a worksheet that will launch a custom form with certain clicks on the cell will be:

 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect(Target, Range("H10,H15")) Is Nothing Then frmCalendar.Show End If End Sub 

I know that there are add-ons that help to do this, but I would like to figure out how to place the user form directly under the above cells (H10, H14, H15) without using an add-in. Do I need to add code to the worksheet or activate sub? What code will be?

Thanks.

EDIT:

I just changed Activate Sub Code to

 Private Sub UserForm_Activate() With frmCalendar .Top = ActiveCell.offset(31).Top .Left = ActiveCell.offset(1).Left End With 

End Sub

This moves it slightly lower and slightly to the right of the cell, but when I try to use it in another cell, it moves further down, but remains at the same distance to the right. It is still dirty. Can't place this form directly under ActiveCell using these methods?

Answer 1

You are using the correct event macro. I put a TextBox on a worksheet and using this macro

 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim s As Shape Set s = ActiveSheet.Shapes(1) s.Top = ActiveCell.Offset(1, 1).Top s.Left = ActiveCell.Offset(1, 1).Left End Sub 

I can make TextBox move right to the right and just below activecell.

Answer 2

I found http://www.vbaexpress.com/forum/archive/index.php/t-22038.html and designed this which I used:

 Sub showUform(iRow&, iCol&) Dim x11!, y11! ActiveSheet.Cells(iRow, iCol).Select x11 = ActiveWindow.PointsToScreenPixelsX(ActiveSheet.Cells(1, 1)) y11 = ActiveWindow.PointsToScreenPixelsY(ActiveSheet.Cells(1, 1)) UserForm1.Left = x11 + ActiveSheet.Cells(iRow, iCol).Left UserForm1.Top = y11 + ActiveSheet.Cells(iRow, iCol).Top UserForm1.Show End Sub 

Answer 3

 Sub FormToActCell(UF As Object, Optional RaD$ = "ACAD", Optional Topw% = 102, _ Optional TopH% = -120) ' form to Active cell or RaD as range address ,offsets topW topH Dim Px&, Py&, Zoomp! If RaD = "ACAD" Then RaD = ActiveCell.Address Set CellToRange = Range(RaD) With CellToRange ' get point about object to Px = (.Left + .Width * Topw / 100) Py = (.Top + .Height * TopH / 100) End With Zoomp = ActiveWindow.Zoom / 100 With UF ' assuming screen as normal pts to pix of 3:4 .Left = Px * Zoomp + ActiveWindow.PointsToScreenPixelsX(0) * 0.75 .Top = Py * Zoomp + ActiveWindow.PointsToScreenPixelsY(0) * 0.75 End With End Sub 

Answer 4

Please see the answer I provided for this question, as I believe this question is the same.

How to align UserForm next to the active cell