Another way to write a code

[brendaattale]

Hello there,

I'm trying to write a code with a format in it but it doesn't seem to work properly. Anyone can help re-write or write it properly with a clean way please? The whole VBA without the whole procedure written is also included. Thank you very much.

Me("STM" & Format(CStr(1+(q * 2)), "00"))

For i = 1 To 12
      For q = 4 To 15
        With Me.Controls("STB" & i)
            If .Value Then
                If Trim(Me("STM" & Format(CStr(1 + (q * 2))), "00")) <> "" Then
                    With Sheets("Inv_doc")
                        'Commands for the sheet
                        With .Range("A17").CurrentRegion
                            'Commands for the table
                            '...
                            'Data transfer to the table
                            j = j + 1
                            With .Rows(.Rows.Count).Offset(1)
                                .Cells(1).Value = "Item " & Format(j, "00") 'Me.Controls("Textbox" & CStr(i)).Name
                                .Cells(2).MergeArea.Cells(1).Value = "Rebate for " & Me("STM" & Format(CStr((q * 2)), "00"))
                              If staWks.Range("P5").Value = "Full" Then
                                .Cells(11).Value = CDec(Me("STM" & Format(CStr(1 + (q * 2)), "00")))
                              ElseIf staWks.Range("P5").Value = "Shared" Then
                                .Cells(11).Value = 2 * CDec(Me("STM" & Format(CStr(1 + (q * 2)), "00"))) 'Shared (2Buy2 standard framework)
                              Else
                                .Cells(11).Value = 1 * CDec(Format(Me.Controls("STM" & CStr(1 + (q * 2))), "00").ValueMe("STM" & Format(CStr(1 + (q * 2)), "00"))) 'Shared + (2Buy2 new framework)
                              End If
                            End With
                        End With
                    End With
                End If
                .Value = False
             End If
        End With
      Next q
    Next i

 

[6StringJazzer]

First you are using Me as either a function name, an array name, or something else. Me is a reserved keyword and it can't be used the way you are using it. What you are trying to do there?

Second, you are giving Format a string but it needs a number. Just drop CStr.

Format(1 + (q * 2), "00")

 

[brendaattale]

Thanks Jeff.
The purpose of the VBA code is to check whether a checkbox is ticked and copy the value in the corresponding textbox to a invoice worksheet by adding the corresponding item before it.
So the code that is working is the following
Me.Controls("Textbox" & CStr(i)).Value or Trim(Me.Controls("TextBox" & CStr(i + 12)).Value). But the issue I have is that the textbox is number from TB08 to TP32 so I need to add "00" for it to be recognised.
Hope this makes sense. Thank you

 

[6StringJazzer]

But the code just says Me( not Me.Controls(

 

[brendaattale]

My apologies. Pretty new with VBA. but it should indeed be controls.
Should I write Me.controls(...?

 

[6StringJazzer]

Your original code just used Me( and the code you showed in post #3 uses Me.Controls(. The code in post #3 is correct way to reference a control.

 

[brendaattale]

Thank you. Can you please let mw me know how would I write it if I have to format it to include double even for a single number so textbox09 instead of textbox9?
This is the reason why I changed the way to write it but it doesn’t seem to work.

 

[6StringJazzer]

Here is what I think you want, see changes below. But I can't test any of it without your file.

For i = 1 To 12
      For q = 4 To 15
        With Me.Controls("STB" & i)
            If .Value Then
                If Trim(Me.Controls("STM" & Format(1 + q * 2, "00")) <> "" Then ' removed Cstr
                    With Sheets("Inv_doc")
                        'Commands for the sheet
                        With .Range("A17").CurrentRegion
                            'Commands for the table
                            '...
                            'Data transfer to the table
                            j = j + 1
                            With .Rows(.Rows.Count).Offset(1)
                                .Cells(1).Value = "Item " & Format(j, "00") 'Me.Controls("Textbox" & CStr(i)).Name
                                .Cells(2).MergeArea.Cells(1).Value = "Rebate for " & Me.Controls("STM" & Format(q * 2, "00")) ' removed Cstr
                              If staWks.Range("P5").Value = "Full" Then
                                .Cells(11).Value = Me.Controls("STM" & Format(1 + q * 2, "00")) ' removed CDec and CStr
                              ElseIf staWks.Range("P5").Value = "Shared" Then
                                .Cells(11).Value = 2 * Me.Controls("STM" & Format(1 + q * 2, "00")) 'Shared (2Buy2 standard framework) ' removed CDec and CStr
                              Else
                               ' I have no idea what you are trying to do on the following link:
                                .Cells(11).Value = 1 * CDec(Format(Me.Controls("STM" & CStr(1 + (q * 2))), "00").ValueMe("STM" & Format(CStr(1 + (q * 2)), "00"))) 'Shared + (2Buy2 new framework)
                              End If
                            End With
                        End With
                    End With
                End If
                .Value = False
             End If
        End With
      Next q
    Next i

 

[brendaattale]

It's exactly what I was looking for. Thanks for your help. Much appreciated.