How do I get macro to run in just one newly created cell in a new table row?

[duteberta]

Here is the VBA script I wrote to add a new table row at top of table and automatically fill in the ID column by running a macro only in that cell. The macro works fine on its own but I cannot get it to work here. The idea is that a new GUID is auto entered into the ID column of my new row.

Sub AddRecordToTable()

    Dim ws As Worksheet
    Dim newRow As ListRow
    
    Set ws = ActiveSheet

    Set newRow = ws.ListObjects("tbl_PROP").ListRows.Add(1)
    
    With newRow
        .Range("PROP[ID]") = GenerateGUID
    

End Sub

 

[StephenCrump]

Does this work for you?

With ws.ListObjects("tbl_PROP")
    .ListRows.Add(1).Range(.ListColumns("ID").Index) = GenerateGUID
End With

 

[duteberta]

Does this work for you?

With ws.ListObjects("tbl_PROP")
    .ListRows.Add(1).Range(.ListColumns("ID").Index) = GenerateGUID
End With

I inserted your code like this and it does not work. The first line gets highlighted in yellow "Sub AddREcordToTable()"

Sub AddRecordToTable()

    Dim ws As Worksheet
    Dim newRow As ListRow
    
    Set ws = ActiveSheet

    Set newRow = ws.ListObjects("tbl_PROP").ListRows.Add(1)
    
'    With newRow
'        .Range("PROP[ID]") = GenerateGUID

    With ws.ListObjects("tbl_PROP")
    
        .ListRows.Add(1).Range(.ListColumns("ID").Index) = GenerateGUID
        
    End With
    

End Sub

 

[StephenCrump]

I inserted your code like this and it does not work. The first line gets highlighted in yellow "Sub AddREcordToTable()"

Do you get an error message? What is that message?

 

[duteberta]

Do you get an error message? What is that message?

Compile error

 

[StephenCrump]

What is GenerateGUID? Have you perhaps inadvertently defined it in more than one way?

 

[duteberta]

What is GenerateGUID? Have you perhaps inadvertently defined it in more than one way?

That macro works perfectly when activated manually. That macro is the following:

Function randBetween(ByVal min As Long, max As Long)
    randBetween = Int(Rnd() * (max - min + 1)) + min
End Function


Function GUID()

Dim guid1, guid2, guid3, guid4, guid5, guid6, guid7, guid8 As String

guid1 = LCase(Hex(randBetween(0, 65535)))
guid2 = LCase(Hex(randBetween(0, 65535)))
guid3 = LCase(Hex(randBetween(0, 65535)))
guid4 = LCase(Hex(randBetween(0, 65535)))
guid5 = LCase(Hex(randBetween(0, 65535)))
guid6 = LCase(Hex(randBetween(0, 65535)))
guid7 = LCase(Hex(randBetween(0, 65535)))
guid8 = LCase(Hex(randBetween(0, 65535)))

guid1 = Right(String(4, "0") & guid1, 4)
guid2 = Right(String(4, "0") & guid2, 4)
guid3 = Right(String(4, "0") & guid3, 4)
guid4 = Right(String(4, "0") & guid4, 4)
guid5 = Right(String(4, "0") & guid5, 4)
guid6 = Right(String(4, "0") & guid6, 4)
guid7 = Right(String(4, "0") & guid7, 4)
guid8 = Right(String(4, "0") & guid8, 4)

GUID = guid1 & guid2 & "-" & guid3 & "-" & guid4 & "-" & guid5 & "-" & guid6 & guid7 & guid8

End Function

Sub GenerateGUID()
Dim rng As Range

For Each rng In Selection
    rng.Value = GUID()
Next rng
End Sub

 

[StephenCrump]

Thanks for posting all your code. That's helpful. Try:

Sub AddRecordToTable()

    Dim ws As Worksheet
    Dim newRow As ListRow
    
    Set ws = ActiveSheet

    With ws.ListObjects("tbl_PROP")
        .ListRows.Add(1).Range(.ListColumns("ID").Index) = GUID
    End With
    
End Sub

I had assumed that your GenerateGUID was a function. Hence, previously, I suggested:

.ListRows.Add(1).Range(.ListColumns("ID").Index) = GenerateGUID

Here's a simple example to illustrate why you're getting the compile error:

'Problem!  Hello1 is a Sub, not a function, and hence doesn't have a value to be assigned to s
'Hence a Compile Error: Expected Function or variable
Sub Test1()

    Dim s As String
    
    s = Hello1

End Sub
Sub Hello1()

    Selection.Value = "Hello"

End Sub
'Using a function. No problem.
Sub Test2()

    Dim s As String
    
    s = Hello2
    MsgBox s

End Sub
Function Hello2()

    Hello2 = "Hello"

End Function

 

[duteberta]

Awesome and perfect! Thank you. I just learned a lot

 

[StephenCrump]

That's great. I'm glad we could help.