0

I have a userform in a sheet called "Dashboard" and a data sheet called "Micrux". Every value of the textbox are suppose to be upload in the "micro" sheet, the problem is that it's upload in my activesheet "Dashboard". Is the reason of my problem cause by the WorksheetFunction? Because I also tried something like this:

emptyRow = WorksheetFunction.CountA(Worksheets("Micrux").Range("C:D"))

And it's still uploading in the wrong sheet.

Private Sub CommandButton1_Enter()

    Dim emptyRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Micrux")
    ActiveSheet.Name = "Dashboard"


    If Not IsDate(TextBox3) Then
        MsgBox " Wrong Format (YYYY/MM/DD)"
        TextBox3 = ""
        Exit Sub
    Else

    End If

    emptyRow = WorksheetFunction.CountA(Range("C:D"))

    Cells(emptyRow + 1, 1).Value = TextBox1.Value
    Cells(emptyRow + 1, 2).Value = TextBox2.Value
    Cells(emptyRow + 1, 3).Value = TextBox3.Value


     Unload Me


End Sub
Marx
  • 106
  • 1
  • 10
Lawrence231
  • 31
  • 7
  • 2
    Change `Cells(emptyRow + 1, 1).Value = TextBox1.Value` to `ws.Cells(emptyRow + 1, 1).Value = TextBox1.Value` and similarly for others. You need to fully qualify the range else it will work with the activesheet – Siddharth Rout Feb 14 '20 at 04:44
  • It now enter the values in the Micrux sheet by the empty row count is based on the Dashboard one. – Lawrence231 Feb 14 '20 at 04:54
  • You need to qualify `Range("C:D")` as well. Please re-read my first comment again about qualifying else what will happen :) – Siddharth Rout Feb 14 '20 at 05:00
  • Oh sorry, now it works pretty well! Thanks again! :) – Lawrence231 Feb 14 '20 at 05:05
  • BTW **1.** `CountA` is not the right way to find the last row. You may want to see [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) **2.** Textbox is not the right way to accept date unless you do a complete error checking. You may want to see [THIS](https://stackoverflow.com/questions/54650417/how-can-i-create-a-calendar-input-in-vba-excel) – Siddharth Rout Feb 14 '20 at 05:13
  • I actually had some trouble with that, thanks a lot you are really helpful!! – Lawrence231 Feb 14 '20 at 05:20

0 Answers0