VBA record data irregardless of worksheet name by user

[coolman]

hi all

I would like know if i want to set my syntax to record the value irregardless of worksheet name, is there anyway to do it? Currently I am able to specify a specific worksheet name as shown in the syntax below.


Dim ws As Worksheet
Set ws = Worksheets("budget tracking")
Dim newrow As Long


If Me.CBBudgetCode.ListIndex <> -1 And Len(Me.TxtBoxPurchaseDate.<wbr>Value) = 10 And Me.TxtBoxPORefNo.Value <> "" And Me.TxtBoxVendorName.Value <> "" And Me.TxtBoxQuantity.Value <> "" And Me.CBUnit.ListIndex <> -1 And Me.TxtBoxRate.Value <> "" And Me.CBLocation.ListIndex <> -1 And Me.TxtBoxTransctionDetails.<wbr>Value <> "" Then


newrow = Application.WorksheetFunction.<wbr>CountA(ws.Range("b:b")) + 2


ws.Cells(newrow, 2).Value = Me.CBBudgetCode.Value
ws.Cells(newrow, 3).Value = Me.TxtBoxPurchaseDate.Value
ws.Cells(newrow, 4).Value = Me.TxtBoxPORefNo.Value
ws.Cells(newrow, 5).Value = Me.TxtBoxVendorName.Value
ws.Cells(newrow, 6).Value = Me.TxtBoxItemPurchased.Value
ws.Cells(newrow, 7).Value = Me.TxtBoxQuantity.Value
ws.Cells(newrow, 8).Value = Me.CBUnit.Value
ws.Cells(newrow, 9).Value = Me.TxtBoxRate.Value
ws.Cells(newrow, 10).Value = Me.TxtAmount.Value
ws.Cells(newrow, 11).Value = Me.CBLocation.Value
ws.Cells(newrow, 12).Value = Me.TxtBoxTransctionDetails.<wbr>Value
End If

 

[Logit]

.
Try

[COLOR=#333333][FONT=arial]Set ws = ActiveWorksheet[/FONT][/COLOR]

 

[Logit]

.
Here is a simple database project for deemonstration :

Option Explicit


Private Sub cmdadd_data_Click()
Dim newRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet
newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1


With ws
' .Unprotect Password:="password"
.Cells(newRow, 1).Value = Me.txtproductcode.Value
.Cells(newRow, 2).Value = Me.txtproductdescription.Value
.Cells(newRow, 3).Value = Me.txtcost.Value
.Cells(newRow, 4).Value = Me.txtcostm2.Value




End With


'clear the data
Me.txtproductcode.Value = ""
Me.txtproductdescription.Value = ""
Me.txtcost.Value = ""
Me.txtcostm2.Value = ""




End Sub

Download link: https://www.amazon.com/clouddrive/share/qLE7xGFjRv61a5UMJA99xKrMlM1jSPSYoz2xJowYbZt

You can open the User Form, change sheets and still enter data on the new sheet.