Excel VBA code using MsgBox Join(Application.WorksheetFunction.Transpose(Range("newch

    I have a command button ("Unique/Srt") on each of my worksheets ("vendor" & "Chart") that extracts unique items and sorts them alphabetically and then it displays the unique and sorted items in a "msgbox" using the code... "MsgBox Join(Application.WorksheetFunction.Transpose(Range("newchart").Value), Chr$(10))" however, I do not know how to get the "msgbox" to scroll & continue to display all the items to the end.
    Also, I would like to by pass the command button and have the "Column G" on the worksheets get automatically and dynamically refreshed and updated at all times.


    Hope someone can help


    *Attached is the workbook.


    Thanks

    Re: Excel VBA code using MsgBox Join(Application.WorksheetFunction.Transpose(Range("n


    Simplest way to 'scroll & continue to display all the items to the end' would be to create a user form (the shape of a message box) with a single listbox filling the entire form and show it rather than the message box. Populate the list box at initialization.

    Code
    Private Sub UserForm_Initialize()
Me.ListBox1.List = Range("newvendor").Value
End Sub

    Re: Excel VBA code using MsgBox Join(Application.WorksheetFunction.Transpose(Range("n


    Thanks NoSparks


    Also, I would like to by pass the command button and have the "Column G" on the worksheets get automatically and dynamically refreshed and updated at all times.


    Appreciate if you can help


    Thanks

    Re: Excel VBA code using MsgBox Join(Application.WorksheetFunction.Transpose(Range("n


    Your command buttons currently call the Vendor and Chart subs.
    Determine the range(s) where a change should cause an update and use the Worksheet_Change event to call the sub.
    (You'll want to disable events while the subs run)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login