将在底部提供问题签名,因为这是相关的,但不是特别的问题。
背景:
我创建了一个绑定到selection_change的用户形式,该已查看了该表单的VBA表单构建器。用户形式中的所有标签都与用户形式的特定页面上的单元格挂钩。
问题:
背景中的临时性很重要...如果您只是尝试将用户形式加载到表上,则Excel崩溃。
我相信问题是在加载用户形式之前,我将用户形式(STB(的标签标记为标签。在标签之前使用stb.show时,我会得到基本表单(我不确定是否可以刷新表单,这可能会更容易(;如果我尝试在stb.show之后卸载STB,那么我仍然必须在发生任何事情之前动作STB(无需查看的力量关闭,将是一个解决方法(。
问题:
是否有一种方法可以使用户形式加载后刷新所有标签字幕?
或,是否有一种方法可以强制卸载用户形式。
第三件事,还有另一种可能更可取的方法(这是主观的;不打算有一百万个选项,但仍然想问(?
代码:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Cells(918, 6).Value = "No" Then Exit Sub
If Target.Count > 1 Then Exit Sub
Dim r As Long, c As Long, i As Long, t As Long
r = ActiveCell.Row
c = ActiveCell.Column
i = 0
Select Case r
Case 25 To 44 '1
i = 52
t = 24
Case 115 To 134 '2
i = 142
t = 114
Case 205 To 224 '3
i = 232
t = 204
Case 295 To 314 '4
i = 322
t = 294
Case 385 To 404 '5
i = 412
t = 384
Case 475 To 494 '6
i = 502
t = 474
Case 565 To 584 '7
i = 592
t = 564
Case 655 To 674 '8
i = 682
t = 654
Case 745 To 764 '9
i = 772
t = 744
Case 835 To 854 '10
i = 862
t = 834
End Select
If i = 0 Then Exit Sub
If Intersect(Target, Range(Cells(t + 1, 3), Cells(t + 20, 3))) Is Nothing Then Exit Sub
With Sheets("Stability")
Stb.Cond1.Caption = .Cells(r, c + 1)
Stb.Cond2.Caption = .Cells(r, c + 1)
Stb.TP01.Caption = .Cells(t, c + 3)
Stb.TP02.Caption = .Cells(t, c + 4)
Stb.TP03.Caption = .Cells(t, c + 5)
Stb.TP04.Caption = .Cells(t, c + 6)
Stb.TP05.Caption = .Cells(t, c + 7)
Stb.TP06.Caption = .Cells(t, c + 8)
Stb.TP07.Caption = .Cells(t, c + 9)
Stb.TP08.Caption = .Cells(t, c + 10)
Stb.TP09.Caption = .Cells(t, c + 11)
Stb.TP10.Caption = .Cells(t, c + 12)
Stb.TP11.Caption = .Cells(t, c + 13)
Stb.TP12.Caption = .Cells(t, c + 14)
Stb.TP13.Caption = .Cells(t, c + 15)
Stb.TP14.Caption = .Cells(t, c + 16)
Stb.TP15.Caption = .Cells(t, c + 17)
Stb.TP16.Caption = .Cells(t, c + 18)
Stb.TP17.Caption = .Cells(t, c + 19)
Stb.TP18.Caption = .Cells(t, c + 20)
Stb.TP19.Caption = .Cells(t, c + 21)
Stb.TP20.Caption = .Cells(t, c + 22)
Stb.TP21.Caption = .Cells(t, c + 23)
Stb.TP22.Caption = .Cells(t, c + 24)
Stb.TP23.Caption = .Cells(t, c + 25)
Stb.TP24.Caption = .Cells(t, c + 26)
Stb.TP25.Caption = .Cells(t, c + 27)
Stb.TP26.Caption = .Cells(t, c + 28)
Stb.TP27.Caption = .Cells(t, c + 29)
Stb.TP28.Caption = .Cells(t, c + 30)
Stb.TP29.Caption = .Cells(t, c + 31)
Stb.TP01x.Caption = .Cells(t, c + 3)
Stb.TP02x.Caption = .Cells(t, c + 4)
Stb.TP03x.Caption = .Cells(t, c + 5)
Stb.TP04x.Caption = .Cells(t, c + 6)
Stb.TP05x.Caption = .Cells(t, c + 7)
Stb.TP06x.Caption = .Cells(t, c + 8)
Stb.TP07x.Caption = .Cells(t, c + 9)
Stb.TP08x.Caption = .Cells(t, c + 10)
Stb.TP09x.Caption = .Cells(t, c + 11)
Stb.TP10x.Caption = .Cells(t, c + 12)
Stb.TP11x.Caption = .Cells(t, c + 13)
Stb.TP12x.Caption = .Cells(t, c + 14)
Stb.TP13x.Caption = .Cells(t, c + 15)
Stb.TP14x.Caption = .Cells(t, c + 16)
Stb.TP15x.Caption = .Cells(t, c + 17)
Stb.TP16x.Caption = .Cells(t, c + 18)
Stb.TP17x.Caption = .Cells(t, c + 19)
Stb.TP18x.Caption = .Cells(t, c + 20)
Stb.TP19x.Caption = .Cells(t, c + 21)
Stb.TP20x.Caption = .Cells(t, c + 22)
Stb.TP21x.Caption = .Cells(t, c + 23)
Stb.TP22x.Caption = .Cells(t, c + 24)
Stb.TP23x.Caption = .Cells(t, c + 25)
Stb.TP24x.Caption = .Cells(t, c + 26)
Stb.TP25x.Caption = .Cells(t, c + 27)
Stb.TP26x.Caption = .Cells(t, c + 28)
Stb.TP27x.Caption = .Cells(t, c + 29)
Stb.TP28x.Caption = .Cells(t, c + 30)
Stb.TP29x.Caption = .Cells(t, c + 31)
Stb.tA.Caption = .Cells(i, c + 1)
Stb.tB.Caption = .Cells(i + 1, c + 1)
Stb.tC.Caption = .Cells(i + 2, c + 1)
Stb.tD.Caption = .Cells(i + 3, c + 1)
Stb.tE.Caption = .Cells(i + 4, c + 1)
Stb.tF.Caption = .Cells(i + 5, c + 1)
Stb.tG.Caption = .Cells(i + 6, c + 1)
Stb.tH.Caption = .Cells(i + 7, c + 1)
Stb.tI.Caption = .Cells(i + 8, c + 1)
Stb.tJ.Caption = .Cells(i + 9, c + 1)
Stb.tK.Caption = .Cells(i + 10, c + 1)
Stb.tL.Caption = .Cells(i + 11, c + 1)
Stb.tM.Caption = .Cells(i + 12, c + 1)
Stb.tN.Caption = .Cells(i + 13, c + 1)
Stb.teO.Caption = .Cells(i + 14, c + 1)
Stb.tP.Caption = .Cells(i + 15, c + 1)
Stb.tQ.Caption = .Cells(i + 16, c + 1)
Stb.tR.Caption = .Cells(i + 17, c + 1)
Stb.tS.Caption = .Cells(i + 18, c + 1)
Stb.tT.Caption = .Cells(i + 19, c + 1)
Stb.tU.Caption = .Cells(i + 20, c + 1)
Stb.tV.Caption = .Cells(i + 21, c + 1)
Stb.tW.Caption = .Cells(i + 22, c + 1)
Stb.tX.Caption = .Cells(i + 23, c + 1)
Stb.tY.Caption = .Cells(i + 24, c + 1)
Stb.tZ.Caption = .Cells(i + 25, c + 1)
'skip row 78
Stb.oA.Caption = .Cells(i + 27, c + 1)
Stb.oB.Caption = .Cells(i + 28, c + 1)
Stb.oC.Caption = .Cells(i + 29, c + 1)
Stb.oD.Caption = .Cells(i + 30, c + 1)
Stb.oE.Caption = .Cells(i + 31, c + 1)
Stb.oF.Caption = .Cells(i + 32, c + 1)
Stb.oG.Caption = .Cells(i + 33, c + 1)
Stb.oH.Caption = .Cells(i + 34, c + 1)
Stb.oI.Caption = .Cells(i + 35, c + 1)
Stb.Show
End With
End Sub
问题签名:
Problem Event Name: APPCRASH
Application Name: EXCEL.EXE
Application Version: 16.0.8431.2236
Application Timestamp: 5a9aa882
Fault Module Name: unknown
Fault Module Version: 0.0.0.0
Fault Module Timestamp: 00000000
Exception Code: c0000005
Exception Offset: 00000000
OS Version: 6.1.7601.2.1.0.256.4
Locale ID: 1033
如果其他人将来有类似的问题,这是我发现有效的答案...
将所有标签字幕代码移至private sub userform_initialize((下的用户形式代码中的子(右键单击userform名称 ->查看代码(,因此该标签在加载时出现。
然后,selection_change代码对适当范围进行检查,然后加载用户形式。
选择change:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Cells(918, 6).Value = "No" Then Exit Sub
If Target.Count > 1 Then Exit Sub
Dim r As Long, t As Long
r = ActiveCell.Row
t = 0
Select Case r
Case 25 To 44 '1
t = 24
Case 115 To 134 '2
t = 114
Case 205 To 224 '3
t = 204
Case 295 To 314 '4
t = 294
Case 385 To 404 '5
t = 384
Case 475 To 494 '6
t = 474
Case 565 To 584 '7
t = 564
Case 655 To 674 '8
t = 654
Case 745 To 764 '9
t = 744
Case 835 To 854 '10
t = 834
End Select
If t = 0 Then Exit Sub
If Intersect(Target, Range(Cells(t + 1, 3), Cells(t + 20, 3))) Is Nothing Then Exit Sub
Stb.Show
End Sub
初始化:
Private Sub UserForm_Initialize()
Dim r As Long, c As Long, i As Long, t As Long
r = ActiveCell.Row
c = ActiveCell.Column
Select Case r
Case 25 To 44 '1
i = 52
t = 24
Case 115 To 134 '2
i = 142
t = 114
Case 205 To 224 '3
i = 232
t = 204
Case 295 To 314 '4
i = 322
t = 294
Case 385 To 404 '5
i = 412
t = 384
Case 475 To 494 '6
i = 502
t = 474
Case 565 To 584 '7
i = 592
t = 564
Case 655 To 674 '8
i = 682
t = 654
Case 745 To 764 '9
i = 772
t = 744
Case 835 To 854 '10
i = 862
t = 834
End Select
With Sheets("Stability")
Stb.Cond1.Caption = .Cells(r, c + 1)
Stb.Cond2.Caption = .Cells(r, c + 1)
Stb.TP01.Caption = .Cells(t, c + 3)
Stb.TP02.Caption = .Cells(t, c + 4)
Stb.TP03.Caption = .Cells(t, c + 5)
Stb.TP04.Caption = .Cells(t, c + 6)
Stb.TP05.Caption = .Cells(t, c + 7)
Stb.TP06.Caption = .Cells(t, c + 8)
Stb.TP07.Caption = .Cells(t, c + 9)
Stb.TP08.Caption = .Cells(t, c + 10)
Stb.TP09.Caption = .Cells(t, c + 11)
Stb.TP10.Caption = .Cells(t, c + 12)
Stb.TP11.Caption = .Cells(t, c + 13)
Stb.TP12.Caption = .Cells(t, c + 14)
Stb.TP13.Caption = .Cells(t, c + 15)
Stb.TP14.Caption = .Cells(t, c + 16)
Stb.TP15.Caption = .Cells(t, c + 17)
Stb.TP16.Caption = .Cells(t, c + 18)
Stb.TP17.Caption = .Cells(t, c + 19)
Stb.TP18.Caption = .Cells(t, c + 20)
Stb.TP19.Caption = .Cells(t, c + 21)
Stb.TP20.Caption = .Cells(t, c + 22)
Stb.TP21.Caption = .Cells(t, c + 23)
Stb.TP22.Caption = .Cells(t, c + 24)
Stb.TP23.Caption = .Cells(t, c + 25)
Stb.TP24.Caption = .Cells(t, c + 26)
Stb.TP25.Caption = .Cells(t, c + 27)
Stb.TP26.Caption = .Cells(t, c + 28)
Stb.TP27.Caption = .Cells(t, c + 29)
Stb.TP28.Caption = .Cells(t, c + 30)
Stb.TP29.Caption = .Cells(t, c + 31)
Stb.TP01x.Caption = .Cells(t, c + 3)
Stb.TP02x.Caption = .Cells(t, c + 4)
Stb.TP03x.Caption = .Cells(t, c + 5)
Stb.TP04x.Caption = .Cells(t, c + 6)
Stb.TP05x.Caption = .Cells(t, c + 7)
Stb.TP06x.Caption = .Cells(t, c + 8)
Stb.TP07x.Caption = .Cells(t, c + 9)
Stb.TP08x.Caption = .Cells(t, c + 10)
Stb.TP09x.Caption = .Cells(t, c + 11)
Stb.TP10x.Caption = .Cells(t, c + 12)
Stb.TP11x.Caption = .Cells(t, c + 13)
Stb.TP12x.Caption = .Cells(t, c + 14)
Stb.TP13x.Caption = .Cells(t, c + 15)
Stb.TP14x.Caption = .Cells(t, c + 16)
Stb.TP15x.Caption = .Cells(t, c + 17)
Stb.TP16x.Caption = .Cells(t, c + 18)
Stb.TP17x.Caption = .Cells(t, c + 19)
Stb.TP18x.Caption = .Cells(t, c + 20)
Stb.TP19x.Caption = .Cells(t, c + 21)
Stb.TP20x.Caption = .Cells(t, c + 22)
Stb.TP21x.Caption = .Cells(t, c + 23)
Stb.TP22x.Caption = .Cells(t, c + 24)
Stb.TP23x.Caption = .Cells(t, c + 25)
Stb.TP24x.Caption = .Cells(t, c + 26)
Stb.TP25x.Caption = .Cells(t, c + 27)
Stb.TP26x.Caption = .Cells(t, c + 28)
Stb.TP27x.Caption = .Cells(t, c + 29)
Stb.TP28x.Caption = .Cells(t, c + 30)
Stb.TP29x.Caption = .Cells(t, c + 31)
Stb.tA.Caption = .Cells(i, c + 1)
Stb.tB.Caption = .Cells(i + 1, c + 1)
Stb.tC.Caption = .Cells(i + 2, c + 1)
Stb.tD.Caption = .Cells(i + 3, c + 1)
Stb.tE.Caption = .Cells(i + 4, c + 1)
Stb.tF.Caption = .Cells(i + 5, c + 1)
Stb.tG.Caption = .Cells(i + 6, c + 1)
Stb.tH.Caption = .Cells(i + 7, c + 1)
Stb.tI.Caption = .Cells(i + 8, c + 1)
Stb.tJ.Caption = .Cells(i + 9, c + 1)
Stb.tK.Caption = .Cells(i + 10, c + 1)
Stb.tL.Caption = .Cells(i + 11, c + 1)
Stb.tM.Caption = .Cells(i + 12, c + 1)
Stb.tN.Caption = .Cells(i + 13, c + 1)
Stb.teO.Caption = .Cells(i + 14, c + 1)
Stb.tP.Caption = .Cells(i + 15, c + 1)
Stb.tQ.Caption = .Cells(i + 16, c + 1)
Stb.tR.Caption = .Cells(i + 17, c + 1)
Stb.tS.Caption = .Cells(i + 18, c + 1)
Stb.tT.Caption = .Cells(i + 19, c + 1)
Stb.tU.Caption = .Cells(i + 20, c + 1)
Stb.tV.Caption = .Cells(i + 21, c + 1)
Stb.tW.Caption = .Cells(i + 22, c + 1)
Stb.tX.Caption = .Cells(i + 23, c + 1)
Stb.tY.Caption = .Cells(i + 24, c + 1)
Stb.tZ.Caption = .Cells(i + 25, c + 1)
'skip row 78
Stb.oA.Caption = .Cells(i + 27, c + 1)
Stb.oB.Caption = .Cells(i + 28, c + 1)
Stb.oC.Caption = .Cells(i + 29, c + 1)
Stb.oD.Caption = .Cells(i + 30, c + 1)
Stb.oE.Caption = .Cells(i + 31, c + 1)
Stb.oF.Caption = .Cells(i + 32, c + 1)
Stb.oG.Caption = .Cells(i + 33, c + 1)
Stb.oH.Caption = .Cells(i + 34, c + 1)
Stb.oI.Caption = .Cells(i + 35, c + 1)
End With
End Sub