How do I align a UserForm next to the active cell

0 votes

When I click in the designated range of cells, a UserForm of a MonthView opens. The basic script was provided in this SO thread. The UserForm is not placed in the desired location.

Here is the script that I used to open the UserForm whenever I clicked any cell in the B3:C2000 range in a certain worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set oRange = Range("B3:C2000")
    If Not Intersect(Target, oRange) Is Nothing Then
        frmCalendar.Top = ActiveCell.Offset(0, 0).Top
        frmCalendar.Left = ActiveCell.Offset(0, 1).Left
    End If
End Sub

Question 1: I have the UserForm StartUpPosition property set to 0 - Manual - is this correct?

Question 2: When I click any cell in the specified range, for the first time after opening the workbook, the UserForm always opens in the far top left corner of the screen. Why?

Question 3: When I click any cell in the specified range, for any clicks after the first, the UserForm opens relative to the previous cell that was active, instead of the one I just clicked. How do I get it to open relative to the cell that just clicked, instead of relative to the previously active cell?

Question 4: Why does it appear to align the bottom of the UserForm instead of the top?

Oct 27, 2022 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

Answer to Q1 - Yes, it's correct. In the code below, I am setting this property in the code.

Answer to Q2 - This question and question #3 may be partly related, in my opinion. It would appear that's where the form opens by default. Attempting to set the form top and left coordinates in the Worksheet SelectionChange event does not function the way your code is now written because the coordinates are never actually set. It is necessary to shift the coordinate setting to the userform initialization event.

Answer to Q3 -  The incorrect placement of the code is another factor in this issue. The coordination setting needs to happen in the userform startup event, as was mentioned previously. My best guess as to why it's referring to the previously active cell is that it doesn't actually change until the worksheet selection change event has finished. The previously active cell is returned because you are attempting to set the coordinates during this event (i.e., before the event ends). Once more, relocating the code to the proper spot resolves this problem.

Answer to Q4 - When it comes to cells (ranges) vs. userforms, there seems to be a distinction in how "top" is defined. The top of the userform appears to be measured from the top of the Excel application, whereas the top of the cell is measured from the first row. To put it another way, if and both equal 144, they will be at different points on the screen. This is due to the fact that the top of activecell is 144 points lower than the first row in the Excel spreadsheet, whereas the top of the userform is 144 points lower than the top of the Excel application (i.e., the top of the Excel window), which is higher on the screen due to the fact that the starting point (top of the Excel window) is higher than the starting point for activecell (first row in the spreadsheet). We may correct for that by increasing the top coordinate by the height of the userform plus the height of the active cell.

answered Oct 27, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

How do I combine the first character of a cell with another cell in Excel?


answered Nov 7, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How do I get it to select a single row based on the value?

Try this: Sub MoveBasedOnValue2() Dim ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How do I reference the cell value within a formula string?

Try: =HYPERLINK("#"&CELL("address",D116),B37) READ MORE

answered Feb 24, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Excel - How do I round a date type to the next hour if it is more than one minute

Add almost 30 minutes and it'll get ...READ MORE

answered Mar 27, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP