VBA causing typed information to go to an incorrec

Question

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

Answer 1

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)