如何在活动单元格旁边对齐用户窗体? [英] How do I align a UserForm next to the active cell?

问题描述

我有一个MonthView的用户窗体，当我单击指定范围的单元格时，该窗体将打开. 此SO线程给了我基本脚本.它没有将UserForm放在我期望的位置.

I have a UserForm of a MonthView that opens when I click in the specified range of cells. This SO thread gave me the basic script. It doesn't put the UserForm where I expect.

这是当我单击范围B3:C2000中的任何单元格时打开用户窗体的脚本(我放置在特定的工作表中).

Here is the script (that I placed in a specific worksheet) to open the UserForm when I click any cell in range B3:C2000.

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

问题1:我将UserForm StartUpPosition属性设置为0 - Manual-这正确吗?

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

问题2:当我单击指定范围内的任何单元格时，在打开工作簿后的第一次，UserForm总是在屏幕的最左上角打开.为什么?

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?

问题3:当我单击指定范围内的任何单元格时，对于第一次单击后的任何单击，UserForm相对于活动的上一个单元格都会打开，而不是我刚刚单击的单元格.如何相对于刚刚单击的单元格而不是相对于先前的活动单元格打开它?

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 just clicked, instead of relative to the previous active cell?

问题4:为什么它看起来像是对齐用户窗体的底部而不是顶部?

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

执行以下步骤后:
1-单击单元格C15
2-用户窗体打开
3-关闭用户窗体
4-单击单元格16
5-用户窗体打开

After I do the following steps:
1 - Click cell C15
2 - UserForm opens
3 - Close UserForm
4 - Click cell 16
5 - UserForm opens

这是我看到的:

以下是实施J. Garth解决方案(并将Offset属性更改为(0，2):

推荐答案

问题1:我将UserForm StartUpPosition属性设置为0-手动-这是否正确?是的，正确.在下面的代码中，我正在代码中设置此属性.

Question 1: I have the UserForm StartUpPosition property set to 0 - Manual - is this correct? Yes, it's correct. In the code below, I am setting this property in the code.

问题2:当我单击指定范围内的任何单元格时，在打开工作簿后的第一次，UserForm总是在屏幕的左上角打开.为什么??我认为答案与问题3有关.这似乎是打开表单的默认位置.现在，您拥有代码的方式试图在Worksheet_SelectionChange事件中设置表单的顶部和左侧坐标不起作用，因为实际上从未设置过坐标.坐标的设置需要移至用户窗体初始化事件.

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? I think the answer to this is somewhat related to question #3. That would seem to be a default location for the form to open in. The way you have the code now, trying to set the form top and left coordinates in the Worksheet_SelectionChange event is not working because the coordinates are never actually getting set. The setting of the coordinates needs to be moved to the userform initialization event.

问题3:当我单击指定范围内的任何单元格时，对于第一次单击后的任何单击，UserForm都相对于活动的上一个单元格打开，而不是我刚刚单击的单元格.如何相对于刚刚单击的单元格而不是相对于先前的活动单元格打开它?此问题还与代码放置在错误的位置有关.如上所述，协调设置需要在用户窗体初始化事件中进行.至于为什么要引用先前的活动单元格，我的猜测是直到工作表选择更改事件完成后，活动单元格才真正被更改.因此，由于您尝试在此事件内(即-在事件结束之前)设置坐标，因此您将获得之前处于活动状态的单元格.同样，将代码移到正确的位置可以解决此问题.

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 just clicked, instead of relative to the previous active cell? This problem is also related to the code being in the wrong place. As noted above, the coordination setting needs to take place in the userform initialization event. As to why it's referencing the previous active cell, my guess would be that the active cell doesn't actually get changed until after the worksheet selection change event completes. So since you are trying to set the coordinates within this event (i.e. - before the event finishes), you are getting the previously active cell. Again, moving the code to the correct location fixes this issue.

问题4:为什么它看起来像是对齐用户窗体的底部而不是顶部?关于单元格(范围)，顶部"的定义似乎有所不同与用户表格.单元格的顶部从第一行开始测量，而用户窗体的顶部似乎从Excel应用程序的顶部开始测量.因此，总而言之，如果activecell.top和userform.top都等于144，则它们在屏幕上的位置将不同.这是因为活动单元格的顶部比Excel电子表格的第一行低144点，而用户窗体的顶部比Excel应用程序的顶部(即-Excel窗口的顶部)低144点.因为起点(Excel窗口的顶部)高于activecell.top(电子表格的第一行)的起点，所以在屏幕上显示.我们可以通过将用户窗体的高度加上活动单元格的高度添加到顶部坐标来对此进行调整.

Question 4: Why does it appear to align the bottom of the UserForm instead of the top? There appears to be a difference between the definition of "top" when it comes to cells (ranges) vs userforms. The top of the cell is measured from the first row whereas the top of the userform seems to be measured from the top of the Excel application. So in over words, if activecell.top and userform.top are both equal to 144, they will be different locations on the screen. This is because the top of activecell is 144 points down from the first row in the Excel spreadsheet while the top of the userform is 144 points down from the top of the Excel application (i.e. - the top of the Excel window), which is higher on the screen because the starting point (top of the Excel window) is higher than the starting point for activecell.top (first row in the spreadsheet). We can adjust for that by adding the height of the userform plus the height of the active cell to the top coordinate.

Private Sub Worksheet_SelectionChange(ByVal target As Range)

    Dim oRange As Range

    Set oRange = Range("B3:C2000")
    If Not Intersect(target, oRange) Is Nothing Then
        frmCalendar.Show
    End If

End Sub

用户代码

Private Sub UserForm_Initialize()

    With Me
        .StartUpPosition = 0
        .Top = ActiveCell.Top + ActiveCell.Height + .Height
        .Left = ActiveCell.Offset(0, 1).Left
    End With

End Sub

这篇关于如何在活动单元格旁边对齐用户窗体?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！