我有一个包含每月工作表的工作簿。一个用于电子邮件,一个用于呼叫,我已经创建了两个用于数据输入的userForms,一个用于电子邮件,一个用于呼叫。
表单完成了工作,它们在正确的位置输入日期,但如果我选择了“8月18日电子邮件”表单并使用电子邮件表单,一旦表单提交,它就会跳转到显示“8月18日呼叫”表单。
我只想让它留在选定的工作表中,在这种情况下是“8月18日电子邮件”。
电子邮件表单的代码如下所示,呼叫表单的代码几乎相同,只是更改了这一行:Set ws = Sheets(Format(Date, "mmmm yy") & " calls")
Private Sub CommandButton2_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet
Set ws = Sheets(Format(Date, "mmmm yy") & " emails")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
If Me.txtDateBox.Value = "" Then
.Cells(lRow, 1).Value = Format(Date, "dd/mmm/yy")
Else
.Cells(lRow, 1).Value = Me.txtDateBox.Value
End If
myVar = ""
For x = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(x) Then
If myVar = "" Then
myVar = Me.ListBox2.List(x, 0)
Else
myVar = myVar & "," & Me.ListBox2.List(x, 0)
End If
End If
Next x
.Cells(lRow, 11).Value = myVar
myVarSign = ""
For x = 0 To Me.ListBox3.ListCount - 1
If Me.ListBox3.Selected(x) Then
If myVarSign = "" Then
myVarSign = Me.ListBox3.List(x, 0)
Else
myVarSign = myVarSign & "," & Me.ListBox3.List(x, 0)
End If
End If
Next x
.Cells(lRow, 12).Value = myVarSign
myVarTheme = ""
For x = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(x) Then
If myVarTheme = "" Then
myVarTheme = Me.ListBox1.List(x, 0)
Else
myVarTheme = myVarTheme & "," & Me.ListBox1.List(x, 0)
End If
End If
Next x
.Cells(lRow, 14).Value = myVarTheme
.Cells(lRow, 2).Value = Me.Time.Value
.Cells(lRow, 3).Value = Me.ComboBox1.Value
.Cells(lRow, 4).Value = Me.ComboBox2.Value
.Cells(lRow, 5).Value = Me.ComboBox3.Value
.Cells(lRow, 6).Value = Me.ComboBox4.Value
.Cells(lRow, 7).Value = Me.ComboBox5.Value
.Cells(lRow, 8).Value = Me.ComboBox15.Value
.Cells(lRow, 9).Value = Me.ComboBox6.Value
.Cells(lRow, 10).Value = Me.ComboBox7.Value
.Cells(lRow, 13).Value = Me.ComboBox11.Value
.Cells(lRow, 15).Value = Me.ComboBox16.Value
.Cells(lRow, 16).Value = Me.TextBox2.Value
End With
Me.txtDateBox.Value = ""
Me.Time.Value = ""
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
Me.ComboBox6.Value = ""
Me.ComboBox7.Value = ""
Me.ComboBox11.Value = ""
Me.ComboBox16.Value = ""
Me.ComboBox15.Value = ""
Me.TextBox2.Value = ""
Dim iCount As Integer
For iCount = 0 To Me!ListBox1.ListCount
Me!ListBox1.Selected(iCount) = False
Next iCount
For iCount = 0 To Me!ListBox2.ListCount
Me!ListBox2.Selected(iCount) = False
Next iCount
For iCount = 0 To Me!ListBox3.ListCount
Me!ListBox3.Selected(iCount) = False
Next iCount
End Sub
它可以改进很多,但如果在提交后,视图中的工作表停留在跳转到另一个工作表,我很高兴。
正如你所看到的,我才刚刚开始(我已经设法在别人的帮助下创建了它)。
发布于 2018-08-23 08:18:37
如果删除工作表、范围或单元格对象上的任何.Select
或.Activate
实例,则工作表不应更改。
如果这不是一种选择,另一种解决方案是注意当代码被调用时你在哪个工作表上,然后在结束你的sub之前Activate
该工作表。因为我们看不到所有的用户表单代码,所以您必须有策略地决定它的位置(正如@K.Davis所提到的,没有显示任何内容来切换工作表,所以它一定是在其他代码中发生的)。
当宏/用户表单启动时:
Dim StartSheet as Worksheet
Set StartSheet = ActiveSheet
然后,在退出宏/用户表单之前:
StartSheet.Activate
您可能必须将其作为参数传递,这取决于您的代码的结构。
https://stackoverflow.com/questions/51975742
复制相似问题