Complex Excel Macro- Need Help

jamieleigh2702

New Member
Joined
Aug 12, 2021
Messages
1
Office Version
  1. 2019
I have a pretty complex excel work book containing multiple macros that I use daily. I am having trouble getting the sheet to allow me to change a range of data that it initially functions on. See below for code to the "Pay Item Entry Dialog" form.
VBA Code:
Private Sub UserForm_Initialize()
    With Me.TableSectionField
        .Clear
        For i = 1 To 12
            .AddItem "tSection" & i
        Next i
        If Not IsNothing(PayItemEntryDialog2RestoreTableName) Then
            .Value = PayItemEntryDialog2RestoreTableName
        End If
    End With
End Sub

'The AddPayItemButton_Click sub only processes the input fields on the dialog.
'It then passes the data to the AddPayItem() Function
Private Sub AddPayItemButton_Click2()
    'On Error GoTo EH
    Dim ErrorNum As Long
    Dim ErrorDescr As String
    Dim TableName As String
    Dim PID As String
    Dim QTY As String
   
    TableName = Trim(Me.TableSectionField.Value)
    If IsNothing(TableName) Then
        MsgBox "No table name"
        'GoTo EH
        Exit Sub
    Else
        If Not TM_TableExists("Tabulation 2 Prototype", TableName) Then
            MsgBox "Table does not exist"
            Exit Sub
        End If
    End If
   
    PID = Trim(Me.PayItemField.Value)
    If IsNothing(PID) Then
        MsgBox "No PID"
        Exit Sub
    Else
       
    End If
   
    QTY = Trim(Me.QuantityField.Value)
    If IsNothing(QTY) Then
        MsgBox "No QTY"
        Exit Sub
    Else
   
    End If
   
    Exit Sub
   
EH:
    MsgBox "ERROR(AddPayItemButton_Click): "
End Sub

Private Sub AddPayItemButton_Click()
    Dim PID As String
    Dim p As Variant
    Dim rng As Range
    Dim tbl As ListObject
    Dim newRow As ListRow
    Dim TableName As String
    Dim zarr As Variant
   
    'Get Pay Item ID from textbox
    PID = Me.PayItemField.Value
    'Test to see if PID is a valid pay item string
    p = PI_IsPayItem(PID) 'returns an array containing three numbers representing the pay item identification
    'if the value of p(0) is above zero then proceed
    If p(0) > 0 Then
        If IsNumeric(Me.QuantityField.Value) And Me.TableSectionField.Value <> "" Then
           
            If TM_TableExists(TA_Worksheet.Name, Me.TableSectionField.Value) Then
                TableName = Me.TableSectionField.Value
                PayItemEntryDialog2RestoreTableName = TableName
                Set tbl = TA_Worksheet.ListObjects(TableName)
                Set newRow = tbl.ListRows.Add
                With newRow
                    .Range(1) = TA_GetNextLID(TableName)
                    .Range(2) = PID
                    .Range(4) = Me.QuantityField.Value
                End With
                'zarr(1) = 1
                lastRow = LastTableRow(TA_Worksheet, tbl)
                ActiveWindow.ScrollRow = lastRow - 10
                Unload Me
                PayItemEntryDialog2.Show
            Else
                MsgBox "ERROR(AddPayItemButton_Click) - Table name does not exist"
            End If
        Else
            MsgBox "ERROR(AddPayItemButton_Click) - Enter a valid quantity"
        End If
    Else
        MsgBox "ERROR(AddPayItemButton_Click) - Not a valid pay item"
    End If
End Sub

My issue is, I need to input a new allowed number sequence that does not meet any exact number format (i.e. the pay item IDs could be anything similar to "a123", "E12-123-12" or "BEC123-45-6-A"..

I need help coming up with a code that will cover all the Pay Item ID codes I give it to process as well as create the sheets and pull the applicable discriptions of the pay item codes as I input them.

Any help or insight is very appreciated.
Thank you
 
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,110
Messages
5,768,156
Members
425,458
Latest member
Jaspal1996

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