Correct language for referencing table

[duteberta]

I'm creating a data entry form that will automatically add new table records when clicking an "Add" button.

In writing the code, instead of referring to

myRow.Range("3") = Range("$C$3")

I need to reference the target table column with the correct syntax. So not "3" but instead something like "MASTER[Status]". It's not working for me.

What language would I use to map correctly? Once I get the correct language I will map all the rest of my fields.

Sub RoundedRectangle1_Click()

    Dim myRow As ListRow
    Dim intRows As Integer
    
    intRows = ActiveWorkbook.Worksheets("MASTER").ListObjects("MASTER").ListRows.Count
    Set myRow = ActiveWorkbook.Worksheets("MASTER").ListObjects("MASTER").ListRows.Add(intRows)
    
        myRow.Range("3") = Range("$C$3")
        


End Sub

 

[duteberta]

Yes you are correct- I mistakenly was adding the 2nd to last row. I will change- thanks

When I run this now- I'm getting a debug error. I have another script that is conflicting because of the use of "listobject.("MASTER")

'Auto sort table on change
Sub SortTable()

    Dim iSheet As Worksheet
    Dim iTable As ListObject
    Dim iColumn As Range

    
    Set iSheet = ActiveSheet
    Set iTable = iSheet.ListObjects("MASTER")
    Set iColumn1 = Range("MASTER[Status]")
    Set iColumn2 = Range("MASTER[CloseD]")
    Set iColumn3 = Range("MASTER[C1]")
    
    With iTable.Sort
    
      .SortFields.Clear
            .SortFields.Add Key:=iColumn1, Order:=xlAscending _
        , CustomOrder:= _
        "PENDING,PEND L,OFFERED,ACT-BUY,ACT-SELL,PRE-LIST,TOM,FUTURE,FUTURE L,SOLD,EXP/WITH/TERM" _
        , DataOption:=xlSortNormal
            .SortFields.Add Key:=iColumn2, Order:=xlDescending
            .SortFields.Add Key:=iColumn3, Order:=xlAscending
            .Header = xlYes
            .Apply
        End With

End Sub

 

[Alex Blakenburg]

1) Why do you think this is a related issue ?
2) What line is highlighted when you click on debug
3) "I have another script that is conflicting because of the use of "listobject.("MASTER")"
What script are you talking about and why would it conflict because you are using listobject Masters

 

[duteberta]

1) Why do you think this is a related issue ?
2) What line is highlighted when you click on debug
3) "I have another script that is conflicting because of the use of "listobject.("MASTER")"
What script are you talking about and why would it conflict because you are using listobject Masters

Set iTable = iSheet.ListObjects("MASTER")

is another line of script from another macro that highlights in the debug when I run this latest macro.

 

[Alex Blakenburg]

Is the other script an event macro ? Ie worksheet change or selection change

 

[duteberta]

Is the other script an event macro ? Ie worksheet change or selection change

It is a worksheet macro... sorry its long- see below

'Follow hyperlinks to hidden sheets

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select
End Sub

'Select entire table row by left-clicking in first column which selects that record for subsequest reporting

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim tbl             As ListObject
    Dim rngCell         As Range
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    On Error Resume Next
    Set tbl = Target.Worksheet.ListObjects(1)
    If tbl Is Nothing Then Exit Sub
    If tbl.ListRows.Count = 0 Then Exit Sub
    
    Set rngCell = Application.Intersect(tbl.ListColumns("X").DataBodyRange, Target)
    If rngCell Is Nothing Then Exit Sub
    On Error GoTo 0
    
    tbl.ListColumns("X").DataBodyRange = ""
    rngCell.Value = 1
End Sub

'Must preceed next Sub

Sub Undo_()
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
End Sub

'Entire table row changes to VALUES after "SOLD" is selected from dropdown


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim rngCol1 As Range
    Dim rng2 As Range
    Dim lo As ListObject
    Dim lColCnt As Long
    Dim i As Long
    Dim v As Variant

    Set lo = Me.ListObjects(1)
    lColCnt = lo.ListColumns.Count

    Set rngCol1 = Intersect(Target, lo.Range.Columns(3))

    If Not rngCol1 Is Nothing Then
        With Application
            .EnableEvents = False
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With

        ReDim v(1 To lColCnt)

        For Each rng In rngCol1
            If LCase(rng.Value) = LCase("SOLD") Then
                i = 0
                
                'Added code
                
                  Dim Answer As VbMsgBoxResult
                  
                    Answer = MsgBox("This will clear all formulas in this row", vbYesNo + vbExclamation + vbDefaultButton2, "CONFIRM MARK SOLD?")
                    
                    If Answer = vbNo Then
                        Call Undo_
                        Exit Sub
                    End If

                'Remember the numerical formats of each column
                For Each rng2 In lo.ListRows(rng.Row - lo.Range.Row).Range
                    i = i + 1
                    v(i) = rng2.NumberFormat
                Next rng2

                '"paste" values
                rng.Resize(, lColCnt).Value = rng.Resize(, lColCnt).Value

                'Restore original formats of each column
                For i = 1 To lColCnt
                    lo.ListRows(rng.Row - lo.Range.Row).Range.Cells(1).Offset(, i - 1).NumberFormat = v(i)
                Next i
            End If
        Next rng

SortTable

        With Application
            .EnableEa
End Sub

 

[Alex Blakenburg]

Are you intending for that code to run the Worksheet Change Event ?
If not put Application.EnableEvents = False towards the start of the code and = True towards the end of the code.