VBA calls typed information to go to the wrong worksheet

Question

I create a spreadsheet that creates a reference number on the first sheet (the so-called database, which will be used similarly to the database) and creates a new worksheet. This then gives the reference number on the new sheet so that they are related to each other. This is done by clicking "New Idea" in UserForm.

Once this is completed, he should go to the newly created sheet and select cell C7. Once this is completed, it should close the UserForm and allow the user to be able to enter a new sheet in cell C7 without additional steps.

This works fine if I use F8 to go through the process, however, if I close the code window and go through the process as a user, it will not work as it should.

C7 is highlighted, but as soon as you typed it and press Enter to go to the cell below, what you typed has completely disappeared, and everything you type on the newly created worksheet is actually entered on another sheet.

I have a separate worksheet containing a button to open UserForm, and all the data entered on the newly created worksheet does not display correctly on this worksheet.

My code is below, and all of this is in UserForm code. I left the ComboBox code below, but this does not apply to generating new worksheets. All that does is list the created tabs so that the user can select a worksheet from UserForm and go straight there, instead of scrolling to the side.

I am using Excel 2013. I am 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 took the liberty of editing and rewriting the code you wrote for more 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 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

, @tomjo , , . , ActiveX.

. , . . , , , , , , , , . , , Debug.Print ThisWorkbook.ActiveSheet.Name, ThisWorkbook.ActiveSheet.ActiveCell.Address. , , , .

, , . , , , , ), .

---- ----

Showform ( ) "" "" , , ShowForm, .

---- ----

Form, sub , , ActiveX (CommandButton) Sheet:

Private Sub CommandButton1_Click()
    Form.Show
End Sub

, .

( )