How to specify Calendar Userform position

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
149
I downloaded a Userform that is a Calendar that that is portable.
This can be downloaded here:
Excel VBA Date Picker

I am using an external monitor as an extended screen.
I have the following code in the Worksheet Selection Change area of code:
VBA Code:
If Not Intersect(Target, Range("B5")) Is Nothing Then
    dateVariable = CalendarForm.GetDate
    If dateVariable <> 0 Then
        ThisWorkbook.Worksheets("Dashboard").Range("B5").value = dateVariable
    End If
End If
If Not Intersect(Target, Range("C5")) Is Nothing Then
    dateVariable = CalendarForm.GetDate
    If dateVariable <> 0 Then
        ThisWorkbook.Worksheets("Dashboard").Range("C5").value = dateVariable
    End If
End If

The problem is that the Calendar opens up on the wrong screen. Can someone figure out how to display the calendar below the selected cell? Or even hover over it?
I emailed the author but he wasn't able to assist me. His reply was:

You can use the PositionTop and PositionLeft arguments to change where the calendar shows up. You’d have to get the position of the range and calculate the top and left positions of the calendar based on that.
It gets a little tricky with multiple monitors, and I unfortunately don’t have an exact code sample to show you, but hopefully you can do some Googling and figure it out.

However, I tried to get it to work based on that but I couldn't figure it out. I can understand how to position Dropdown menus, pictures etc. But with this it is using the Calendar Userform as a function and I can't understand how to still use it as a function and still be able to position the calendar properly.

Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
I did put this code in the userform initialize it might work.

VBA Code:
Private Sub UserForm_Initialize()
    '-------------------------------
    Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
    Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * Me.Height)
    '-------------------------------
End Sub
 

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
149
I did put this code in the userform initialize it might work.

VBA Code:
Private Sub UserForm_Initialize()
    '-------------------------------
    Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
    Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * Me.Height)
    '-------------------------------
End Sub
The Calendar on the site was quite complicated. The Userform Initialize was not used. If you download it you can see. However, the author put in another section labelled:

Private Sub InitializeUserform(SelectedDate As Date, MinimumDate As Date, MaximumDate As Date, _
RangeOfYears As Long, _
PositionTop As Long, PositionLeft As Long, _
SizeFont As Long, bWeekNumbers As Boolean, _
BackgroundColor As Long, _
HeaderColor As Long, _
HeaderFontColor As Long, _
SubHeaderColor As Long, _
SubHeaderFontColor As Long, _
DateBorder As Boolean, DateBorderColor As Long, _
DateSpecialEffect As fmSpecialEffect)

In that section, I was able to modify it to my needs. Thank you for your insight. I used your code as a base to get it to work.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top