VBA code, user form data entry to table specific to worksheet

Taconumber

New Member
Joined
Dec 5, 2021
Messages
4
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Howdy,

I am new to VBA and am looking for some help with a macro.

A bit of the background, i am building myself a monthly budget spreadsheet to keep track of expenses and income.

I trying to code a button that i can add a expense for a month to a table within a specific worksheet which correlates to the month. I have got it somewhat working but i am stuck on assigning the entry to the specific months worksheet. my code is
VBA Code:
Private Sub UserForm_Activate()
expcatE.RowSource = "cats"
expsubcat.RowSource = "subcat"
exptax.RowSource = "taxopt"
expmonth.RowSource = "month"
expfreq.RowSource = "freq"

End Sub
Private Sub CANCELBTN_CLICk()
add_expense.Hide
Unload add_expense

End Sub

Private Sub OKb_click()

targetsheet = expmonth.Value

Dim name As String
Dim budget As Double
Dim cost As Double
Dim catergory As String
Dim subcatergory As String
Dim frequency As String
Dim tax As String

name = expname.Text
budget = expbudg.Text
cost = expcost.Text
catergory = expcatE.Text
subcatergory = expsubcat.Text
frequency = expfreq.Text
tax = exptax.Text

Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("expense")
Dim newrow As ListRow
Set newrow = tbl.ListRows.Add
With newrow
    .Range(1) = name
    .Range(2) = budget
    .Range(3) = catergory
    .Range(4) = subcatergory
    .Range(5) = frequency
    .Range(7) = cost
    .Range(9) = tax
  End With

With ws.ListObjects("EXPENSE").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("EXPENSE[CATERGORY]"), Order:=xlAscending
    .Apply
End With

MsgBox ("data is added sucessfully")

add_expense.Hide
Unload add_expense
End Sub
So i have figured the code "targetsheet = expmonth.Value" where expmonth is a drop down combo box of the months. If i am not putting the data into a table this works to direct the userform data to the right sheet within a range of cells (1,1 2,4) etc. As you can see from my screen shot i have the dashboard of each month in a different sheet. I know a table cant have the same name across the sheets in the whole work book. I cant figure out how to get the data to export to a table in the corresponding month. If i name the table on the different sheets (expensejul, expensesaug, expensesep) etc would adding something like the code below work?

VBA Code:
If targetsheet = "august" Then
Set tbl = ws.ListObjects("expenseaug")


I hope it make sense and someone can guide me in the right direction. I have been enjoying what i have learnt in a few days of VBA! i am sure there is going to be many more questions to come.
 

Attachments

  • worksheet snip.JPG
    worksheet snip.JPG
    178.4 KB · Views: 4

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,181,218
Messages
5,928,749
Members
436,627
Latest member
caligirl626

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top