Popup Box location

[TimlmiT]

Gurus,

Is there a way to specify the location (position relative to the active cell) as to where a popup box will open?

TIA,
Tim

[Leith Ross]

Hello Tim,

What type of popup are you referring to? An Application.InputBox will allow you to select the position. A MsgBox defaults to the center of the screen. It is possible to change but it requires a lot of API code and is not really worth the time and effort.

You can also use a UserForm to create a custom popup. It can be positioned where you want with a little extra coding.

[TimlmiT]

Leith,

I am not sure if you received the thread that I sent a few days ago.

To answer your question, the box was created using UserForm.

Is the code to position that popup difficult? Any help would be appreciated.

Thanks,
Tim

[TimlmiT]

Leith,

I should have been more specific. The box was created using Userform. I named it frmCalendar if that helps. I was hoping that code could be written.

Basically, I have it set up so that when the user clicks on any cell in row2, the Calendar box pops up. However, it pops up in the middle of the worksheet. I would like to get it to pop up closer to row2.

Tim

[jaslake]

Hi Tim

This is the Code I use.

'****************************************************
 'The 2 macros below are mutually exclusive, use one OR the other
 '****************************************************
 
 '<< Code for userform (macro 1) >>
Option Explicit 
 
Private Sub UserForm_Activate() 
    With UserForm1 
        .Top = Application.Top + 125 '< change 125 to what u want
        .Left = Application.Left + 25 '< change 25 to what u want
    End With 
End Sub 
 
 
 
 
 'OR use
 
Option Explicit 
 
 Private Sub UserForm_Activate() 
    With UserForm1 
        .Top = 125 '< change 125 to what u want
        .Left = 25 '< change 25 to what u want
    End With 
End Sub

[nilem]

Hi Tim,
maybe this example will be useful for you (see attachment)

[buran]

Maybe something like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim intXoffset As Integer
Dim intYoffset As Integer
    If Not Intersect(ActiveCell, Rows(2)) Is Nothing Then
        Load frmCalendar
        With frmCalendar
            .StartUpPosition = 0
            intXoffset = ActiveWindow.PointsToScreenPixelsX(Range("A1"))
            intYoffset = ActiveWindow.PointsToScreenPixelsY(Range("A1"))
            .Top = intYoffset '+ ActiveCell.Top
            .Left = intXoffset + ActiveCell.Left
            .Show
        End With
    End If
End Sub

[TimlmiT]

Thank you all for your input.

For some reason or another the only one that worked was Buran's code. However, I tweaked it a bit with code from the others.

Below is what I ended up with.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim intXoffset As Integer
Dim intYoffset As Integer
If Intersect(Target, Range("a2:a30")) Is Nothing Then Exit Sub
Cancel = True
With frmCalendar
    .StartUpPosition = 0
    intXoffset = ActiveWindow.PointsToScreenPixelsX(Range("c2"))
    intYoffset = ActiveWindow.PointsToScreenPixelsY(Range("c2"))
    .Top = intYoffset + ActiveCell.Top - 150
    .Left = intXoffset + ActiveCell.Left + 175
    .Show
End With
End Sub

FYI - until I changed the range to c2 in the intXoffest and intYoffset lines, it failed. Seems like it had to do with the size of columns A and B as well as the size of the UserForm.

Thanks Again!!!
Tim

[jaslake]

Hi Tim

The Code I provided would go into the Private Sub UserForm_Activate() event.

[buran]

the others have StartUpPosition property of the user form set to manual at design time and they didn't mention it in their posts. I had it in my code, set on the fly, and that's why mine works.