I’m very new to VBA.

I’m trying to create a library of exercises, every time you create a new exercise, you add the details to the library. The form consists of combo boxes, text boxes and option buttons.

I’ve trawled through these forums and youtube and i’ve tried various methods of creating a form, and getting the data from that form into a table in excel, whether that’s adding a new row or adding the data to the next empty row – either is fine.

My main form doesn’t seem to recognise the range/table to add data to the next blank row? It seems to fail at the bold line below.

Private Sub cmdadd_Click()

Dim Sector As String
Dim Objective As String
Dim ExName As String
Dim TrafficLevel As String
Dim EGHI As Integer
Dim EGHH As Integer
Dim EGLF As Integer
Dim OVFI As Integer
Dim OVFV As Integer
Dim HIA As Integer
Dim HID As Integer
Dim HHA As Integer
Dim HHD As Integer
Dim LFD As Integer
Dim INF As String
Dim CAS As String
Dim INC As String
Dim MA As String
Dim ABES1 As String
Dim ABES2 As String
Dim ABES3 As String
Dim ABES4 As String
Dim ART1 As String
Dim ART2 As String
Dim ART3 As String
Dim ART4 As String
Dim CMT As String


Sector = cmbSector.Value
Objective = cmbObj.Value
ExName = txtName.Value
OVFI = txtIFR.Value
OVFV = txtVFR.Value
HIA = txtHIA.Value
HID = txtHID.Value
HHA = txtHHA.Value
HHD = txtHHD.Value
LFD = txtLFD.Value
CMT = txtComments.Value
ABES1 = cmbABES1.Value
ABES2 = cmbABES2.Value
ABES3 = cmbABES3.Value
ABES4 = cmbABES4.Value
ART1 = cmbART1.Value
ART2 = cmbART2.Value
ART3 = cmbART3.Value
ART4 = cmbART4.Value

If optLow.Value = True Then
    TrafficLevel = "L"
ElseIf optMed.Value = True Then
    TrafficLevel = "M"
ElseIf optHigh.Value = True Then
    TrafficLevel = "H"
End If

If opt02.Value = True Then
    EGHI = "02"
ElseIf opt20.Value = True Then
    EGHI = "20"
End If

If opt08.Value = True Then
    EGHH = "08"
ElseIf opt26.Value = True Then
    EGHH = "26"
End If

If opt06.Value = True Then
    EGLF = "06"
ElseIf opt24.Value = True Then
    EGLF = "24"
End If


With Table2
Dim rw As Integer
**rw = .Range("A" & .Rows.Count).End(x1up).Row + 1**


.Range("A" & rw).Value = Sector
.Range("B" & rw).Value = Objective
.Range("F" & rw).Value = ExName
.Range("G" & rw).Value = TrafficLevel
.Range("H" & rw).Value = EGHI
.Range("I" & rw).Value = EGHH
.Range("J" & rw).Value = EGLF

End With



End Sub

At the moment i’ve got a basic (test) example which is adding a new row, but not dragging the data across and if i could get this working i was going to transfer it onto the above example:

Public Sub InsertDataIntoTable()

Dim tableName As ListObject
Set tableName = Sheet6.ListObjects("Table6")
Dim addedRow As ListRow
Set addedRow = tableName.ListRows.ADD()
With addedRow
.Range(1) = txtsector
.Range(2) = txtobj
End With

End Sub

I’ve even copied some examples i’ve seen online like for like, but they all seem to hit a problem and i don’t have the knowledge to problem-solve it. Any suggestions would be greatly appreciated

I’ve tried the examples above. i’m trying to enter the data into the form, click ‘add’ command button, and then transfer the data from the form onto the table as a library/database of exercises, so that i can later search/filter to find what i want