VBA导致键入的信息转到不正确的工作表 [英] VBA causing typed information to go to an incorrect worksheet
问题描述
我正在创建一个在第一个工作表上创建引用号的电子表格（称为数据库，与数据库类似），并生成一个新的工作表。然后，在新的工作表上给出一个参考号，以便将它们链接在一起。这是通过在UserForm上按新建来完成的。
一旦完成，它应该转到新创建的工作表，并突出显示单元格C7。一旦完成，它应该关闭UserForm并允许用户能够在新工作表上键入单元格C7，而不需要进一步的步骤。
如果我使用F8来完成该过程，那么如果我关闭代码窗口并运行该进程，因为用户将无法正常工作应该。
C7被突出显示，但一旦你输入了它，然后按Enter键进入下面的单元格，你输入的内容完全消失，无论你在新的生成的工作表实际上是在其他工作表上输入的。
我有一个单独的工作表，其中包含一个按钮来打开UserForm，并且在新生成的工作表上输入的所有数据都将转到这张表不正确。
我的代码在下面，都在UserForm的代码中。我已经将ComboBox代码放在下面，但这与新工作表的生成无关。所有这一切都列出了创建的选项卡，所以用户可以从UserForm中选择一个工作表，直接在那里，而不必侧翻。
我正在使用Excel 2013我绝对不是一个VBA老将，所以任何帮助将不胜感激！
谢谢！
Private Sub UserForm_Initialize（）
Me.ComboBox1.List = Worksheets（Database）。Range（A2 ：A10000）值
End Sub
Private Sub CreateNewIdea_Click（）
CopySheet
End Sub
Sub CopySheet（）
Dim LastRow As Long
NewReference
LastRow = Sheets（Database）。Range（A& Rows.Count）.End（xlUp）.Row - 1
ReturnValue = LastRow
Sheets（Idea Template）。复制之后：= Sheets（Sheets.Count）
ActiveSheet.Name = LastRow
范围（C3）。Value = LastRow
Me.ComboBox1.List = Worksheets（Database）。Range（A2：A10000）。value
Range（C7）。选择
卸载首页
End Sub
Sub NewReference（）
Dim LastRow As Long
LastRow = Sheets（Database）。Range（A& Rows.Count）.End（xlUp）.Row
Sheets（Database）。Cells（LastRow + 1，A）。Value = Sheets（Database）。Cells（LastRow，A）。Value + 1
End Sub
Private Sub ComboBox1_Change（）
工作表（ComboBox1 .Text）。选择
End Sub
我已经采取自由编辑和重写您编写的代码，以获得更大的灵活性。
Option Explicit'强制变量被声明，未声明的变量不允许
Dim DatabaseTable As ListObject'使变量可用于整个模块
Dim Lastrow As Long
Private Sub UserForm_Initialize（）
设置DatabaseTable = ThisWorkbook.Worksheets（数据库）。ListObjects（References）
'我假设你已经在工作表上格式化数据表，并将其命名为参考文献
Dim i As Long
Dim DatabaseRows As Long
DatabaseRows = DatabaseTable.ListRows.Count
With Me.ComboBox1
.Value =空
.Clear
对于i = 1到DatabaseRows
.AddItem DatabaseTable.DataBodyRange（i，1）
Next i
结束
End Sub
Private Sub CreateNewIdea_Click（）
Set DatabaseTable = ThisWorkbook.Worksheets（Database）。ListObjects（References）
调用CopySheet
End Sub
Sub CopySheet（）'你是否从Private Sub CreateNewIdea_Click（）之外的其他子类调用Sub CopySheet（）？
调用NewReference
Dim ReturnValue As Long'我声明这个变量，因为我使用的是显式的选项，并且不允许未声明的变量
ReturnValue = Lastrow'除非ReturnValue是一个public变数，这不是很多。
ThisWorkbook.Worksheets（Idea Template）。复制之后：= ThisWorkbook.Worksheets（Worksheets.Count）
ThisWorkbook.Worksheets（Idea Template（2））。name = Lastrow
ThisWorkbook.Worksheets（CStr（Lastrow））。单元格（1，3）.Value = Lastrow'Cstr（lastrow）是需要的，因为我们想要的是最后一行的名称，而不是第n个工作表，这与WorkSheets会发生什么（Lastrow）as lastrow是一个数字
调用UserForm_Initialize调用填充ComboBox1的过程，如果卸载home引用此表单，则该行是冗余的，因为在初始化表单时，该组合框将再次被填充。
ThisWorkbook.Worksheets（CStr（Lastrow））。单元格（7，3）。选择
卸载首页'如果此表单的名称在家，您可以只'卸载我'
End Sub
Sub NewReference（）'你是否从Sub CopySheet（）之外的其他子文件中调用Sub NewReference？
DatabaseTable.ListRows.Add AlwaysInsert：= False'将新行添加到工作表数据库上的表
Lastrow = DatabaseTable.ListRows.Count
如果Lastrow = 2和IsEmpty（DatabaseTable.DataBodyRange（1，1）））然后'如果确定是否添加一行，而第一行不包含引用
DatabaseTable.DataBodyRange（Lastrow，1）.Value = 1'第一个参考，可以是你想要的任何东西
DatabaseTable.ListRows（1）.Delete'第一行被删除，否则你将有一个空的第一行
Lastrow = Lastrow - 1
Else
DatabaseTable.DataBodyRange（Lastrow，1）.Value = DatabaseTable.DataBodyRange（Lastrow - 1，1）+ 1
End If
End Sub
Private Sub ComboBox1_Change（）
如果不是Me.ComboBox1 =空然后
工作表（CStr（Me.ComboBox1））。选择
如果
End Sub
修订答案
在查看@tomjo提供的文档并尝试重新排列问题后，我发现问题是由于工作表上的按钮引起的。使用的按钮是窗体控件，而不是ActiveX控件。
将宏分配给窗体控件宏按照您的预期在模块中定义。宏只要求显示相关表格。看起来好像选择的表格，或者是通过窗体上的菜单或从表单创建新的表格之后没有被正确激活，并且输入到显示的工作表中的信息实际上是以最后手动选择的形式输入的。通过代码，我发现通过表单通过 Debug.Print ThisWorkbook.ActiveSheet.Name，ThisWorkbook.ActiveSheet.ActiveCell.Address 选择后，正确的表单和单元格是活动的。我没有发现为什么在正确的工作表和单元格处于活动状态时，信息已在最后手动选择的工作表和单元格中输入。
要确认问题不是由文件导致，我试图用一个全新的文件重现问题。再次出现的问题是，当屏幕显示正确的工作表和单元格被选定（通过一个窗体，通过模块中的一个子项调用，由表单控件调用）时，信息实际上是在最后手动选择的单元格和单元格中输入的。
----编辑----
运行Showform宏（调用窗体到显示）从功能区的开发人员选项卡下的Macros按钮，而不是单击指定了ShowForm宏的Form控件按钮，不会产生问题。
----结束编辑----
然后我只删除了Form控件，而在模块中的sub调用来显示表单并放置在工作表和相关工作表模块上创建一个ActiveX控件（CommandButton）：
Private Sub CommandButton1_Click（）
Form.Show
End Sub
没有进一步编辑代码，没有进一步的问题REGA在最后手动选择的纸张和单元格上输入的信息，而不是屏幕显示为选定的纸张和单元格。
编辑文件（该链接将在有限的时间内处于活动状态） p>
I am creating a spreadsheet that creates a reference number on the first worksheet (called database, to be used similarly to a database) and generates a new worksheet. This then gives a reference number on the new worksheet so that they are linked together. This is done by pressing "New Idea" on a UserForm.
Once this is completed it should then go to the newly created worksheet and highlight cell C7. Once this is complete it should close the UserForm and allow the user to be able to type in cell C7 on the new worksheet with no further steps needed.
This works fine if I use F8 to step through the process however if I close the code window and run through the process as a user would it doesn't work as it should.
C7 is highlighted but once you have typed in it and press enter to go to the cell below, what you've typed disappears completely, and whatever you type on the newly generated worksheet is actually entered on another worksheet.
I have a seperate worksheet that contains a button to open the UserForm and all data that is entered on the newly generated worksheet goes to this sheet incorrectly.
My code is below, and is all within the UserForm's code. I have left the ComboBox code below but this isn't relevant to the generation of the new worksheets. All that does is list the created tabs so the user can select a worksheet from the UserForm and go directly there rather than having to scroll sideways.
I'm using Excel 2013. I'm by no means a VBA veteran so any help would be greatly appreciated!
Thanks!
Private Sub UserForm_Initialize()
Me.ComboBox1.List = Worksheets("Database").Range("A2:A10000").Value
End Sub
Private Sub CreateNewIdea_Click()
CopySheet
End Sub
Sub CopySheet()
Dim LastRow As Long
NewReference
LastRow = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row - 1
ReturnValue = LastRow
Sheets("Idea Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = LastRow
Range("C3").Value = LastRow
Me.ComboBox1.List = Worksheets("Database").Range("A2:A10000").Value
Range("C7").Select
Unload Home
End Sub
Sub NewReference()
Dim LastRow As Long
LastRow = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Database").Cells(LastRow + 1, "A").Value = Sheets("Database").Cells(LastRow, "A").Value + 1
End Sub
Private Sub ComboBox1_Change()
Worksheets(ComboBox1.Text).Select
End Sub
I've taken the liberty to edit and rewrite the code you've written for greater flexibility.
Option Explicit 'Forces the variable to be declared, undeclared variables are not allowed
Dim DatabaseTable As ListObject 'Makes the variable usable for the entire module
Dim Lastrow As Long
Private Sub UserForm_Initialize()
Set DatabaseTable = ThisWorkbook.Worksheets("Database").ListObjects("References")
'I'm assuming you've formatted the data on the worksheet as a table and named the table "References"
Dim i As Long
Dim DatabaseRows As Long
DatabaseRows = DatabaseTable.ListRows.Count
With Me.ComboBox1
.Value = Empty
.Clear
For i = 1 To DatabaseRows
.AddItem DatabaseTable.DataBodyRange(i, 1)
Next i
End With
End Sub
Private Sub CreateNewIdea_Click()
Set DatabaseTable = ThisWorkbook.Worksheets("Database").ListObjects("References")
Call CopySheet
End Sub
Sub CopySheet() 'Are you calling Sub CopySheet() from other subs besides Private Sub CreateNewIdea_Click()?
Call NewReference
Dim ReturnValue As Long 'I'm declaring this variable because I'm using the option explicit and that doesn't allow undeclared variables
ReturnValue = Lastrow 'Unless ReturnValue is a public variable, it's not doing much here.
ThisWorkbook.Worksheets("Idea Template").Copy After:=ThisWorkbook.Worksheets(Worksheets.Count)
ThisWorkbook.Worksheets("Idea Template (2)").name = Lastrow
ThisWorkbook.Worksheets(CStr(Lastrow)).Cells(1, 3).Value = Lastrow 'Cstr(lastrow) is needed because we want the sheet with the name of the last row, not the nth sheet which is what happens with WorkSheets(Lastrow) as lastrow is a number
Call UserForm_Initialize 'Calls the procedure which fills ComboBox1, if the unload home refers to this form, then this line is redundant since the combobox is filled again when the form is initialized.
ThisWorkbook.Worksheets(CStr(Lastrow)).Cells(7, 3).Select
Unload Home 'If the name of this form is home, you can just 'Unload Me'
End Sub
Sub NewReference() 'Are you calling Sub NewReference from other subs besides Sub CopySheet()?
DatabaseTable.ListRows.Add AlwaysInsert:=False 'Adds a new row to the table on the worksheet "Database"
Lastrow = DatabaseTable.ListRows.Count
If Lastrow = 2 And IsEmpty(DatabaseTable.DataBodyRange(1, 1)) Then 'This if determines if a row was added while the first row does not contain a reference
DatabaseTable.DataBodyRange(Lastrow, 1).Value = 1 'First reference, can be anything you want really
DatabaseTable.ListRows(1).Delete 'First row is deleted, otherwise you'd have an empty first row
Lastrow = Lastrow - 1
Else
DatabaseTable.DataBodyRange(Lastrow, 1).Value = DatabaseTable.DataBodyRange(Lastrow - 1, 1) + 1
End If
End Sub
Private Sub ComboBox1_Change()
If Not Me.ComboBox1 = Empty Then
Worksheets(CStr(Me.ComboBox1)).Select
End If
End Sub
Revised answer
After looking at the document provided by @tomjo and trying to reporduce the problem I found that the problem was caused by the buttons on the sheets. The buttons used were the Form Controls rather than ActiveX Controls.
A macro was assigned to the Form Control The macro was defined in a module as you'd expect. The Macro only called to show the relevant form. It appeared as if the selected sheet, either by the menu on the form or after creating a new sheet from the form wasn't properly activated and the information entered in the sheet that showing was actually entered in the form which was last manually selected. Stepping through the code I found that the proper sheet and cell was active after the selection through the form through Debug.Print ThisWorkbook.ActiveSheet.Name, ThisWorkbook.ActiveSheet.ActiveCell.Address. I failed to discover why, while the correct sheet and cell were active, the information was entered in the last manually selected worksheet and cell.
To verify that the issue was not caused by the file somehow I tried to reproduce the problem with an entirely new file. Again the problem occurred that while the screen was showing the correct sheet and cell as selected (selected through a form, called by a sub in a module, called by a Form Control) the information was actually entered in the last manually selected sheet and cell.
----Edit----
Running the Showform macro (which calls the form to show) from the Macros button under the developer tab in the ribbon, instead of clicking the Form control button which has the ShowForm macro assigned to it, doesn't create the issue.
----End of Edit----
I then only removed the Form control, and the sub in the module which calls to show the form and placed an ActiveX Control (CommandButton) on the sheet and on the relevant Sheet module created:
Private Sub CommandButton1_Click()
Form.Show
End Sub
Without editing the code any further, there were no further issues regarding information being entered on the last manually selected sheet and cell instead of the sheet and cell the screen was showing as selected.
Edited file (The link will be active for a limited time)
这篇关于VBA导致键入的信息转到不正确的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
