Form Control Buttons - Cell reference - Macro

[TDC21]

I created a collections form from several linked tables. On our aged receivables table I added a cell with a validated list were a customer ID can be entered. There is an associated macro that will go to the customer account detail form and update the filter with the selected ID from the Aging table. This way collections can review the overall current status and then jump to a single customer and find Invoice detail, credit balances, terms, contacts, call notes, ect...

It works pretty smoothly however I am wondering if there is a way to simplify selecting a customer from the aging table. Right now you type or paste the ID you want detail on into a selected cell and then click the button. I would like each table line to have its own button to eliminate the data entry process all together if possible. Is there a way to do this without writing a new macro for each line?

 

[PatOBrien198]

Could you use "Data Validation" to create a drop down selection box for each row?

 

[TDC21]

The way it works now is as a select ID (Which dose use data Validation) then a update and go to macro, it takes you from the Aging Report for all customers to the customer detail form of the selected customer. The only issue is that the select ID part is a manual data entry, its not that big of a deal, I am just trying to see if there is a way to eliminate the data entry altogether though.

Each row on the Aging table is for a individual customer, so I am trying to think of a way to create a button for each row with a macro that will jump to another worksheet, update a cell "which prompts customer filters" based on the value in Column A and the Row that the button is associated with. I could create a macro for each button but that could be several hundred of them. I am sure there is a way to do this through VBA but my IQ in that department still needs lots of improvement. Everything I have so far has been via a simple record macro and I dont know if there are any obvious options that I may be missing.

Appreciated as always, thanks everyone.

 

[PatOBrien198]

Could you use the Double-Click event rather than a button?

Test to make sure that the user has clicked the A column on the sheet with the customer Aging table.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)   

 If Sh.Name = "YourCustomerListAging sheet name" Then
        If Target.Column = 1 Then ' test for "A" column
            Call YourMacro(Target.Value)
            Cancel = True
        End If
    End If

End Sub

 

[TDC21]

Thanks Pat, I will play with this and see how it goes. Appreciate the help

 

[TDC21]

So like I said, my VBA IQ is not that advanced but it looks like the code you provided is basically saying when in the "AGED SUMMARY" sheet, on double click, run My Macro? Not actually sure if that is correct but if so there will be an issue.

My Macro updates Cell H:2 in Sheet "FORM" with the value that is in Cell C:1 on Sheet "AGED SUMMARY", it then makes Sheet "FORM" the active sheet. Because Cell C:1 is a manual input currently running the macro would just update the FORM sheet with whatever is in that cell.

If there is a way to create a on double click macro it would need to work like,

When double click on column A in worksheet "AGED SUMMARY", Record value double clicked on, update Cell H:2 in Worksheet "FORM" with value that was double clicked on, open worksheet "FORM"

 

[PatOBrien198]

If there is a way to create a on double click macro it would need to work like,

When double click on column A in worksheet "AGED SUMMARY", Record value double clicked on, update Cell H:2 in Worksheet "FORM" with value that was double clicked on, open worksheet "FORM"

Well, that's kind of what it does....

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)   

 If Sh.Name = "AGED SUMMARY" Then  ' this is the test for checking the double click only on the "AGED Summary" sheet
        If Target.Column = 1 Then          ' this is the test for the double click in the "A" column
            Call YourMacro(Target.Value)  ' this is where the value of the double clicked cell gets passed to your macro
            Cancel = True
        End If
    End If

End Sub

'------- the above code goes in the "ThisWorkbook" code module


'------- in a separate code module
Public Sub YourMacro(MyValue)

    If MyValue <> "" Then
        Sheets("FORM").Select
        Range("H2") = MyValue
    End If
End Sub

 

[TDC21]

Thanks Pat, that was pretty cool. I stumbled around for a bit thinking I needed to change the name of "YourMacro" to my macros name for a bit, still not exactly sure how that is working without the specific designation but it is working great now and I learned a few things in the process.