How do I display the most recent Excel sheet row when opening a User form in VBA ?

Hi Toco,

I prepared a sample workbook with 3 values in column A and a button to show the user form.

This displays the value in last populated cell in column A.

Procedure to display user form called "UserForm" (assigned to button "Load UserForm" in "Sheet 1":

Sub LoadUserForm()

UserForm.Show

End Sub

Select allOpen in new window

Procedure for initialization of the user form, which displays the value of last populated cell in column A:

Private Sub UserForm_Initialize()

DisplayMostRecentValue.Caption = Cells(1, 1).End(xlDown).Value

End Sub

Select allOpen in new window

I've also added a button for closure of the user form so that user does not need to click on red cross (this could be disabled):

Private Sub Close_UserForm_Click()

Unload UserForm

End Sub

Select allOpen in new window

ShowMostRecentValue.xlsm

Hi,

I'm struggling to convert your code to mine. My UserForm_Initialize() routine doesn't do anything. I've changed the 'UserForm' in that statement to 'ufOrdersForm_Initialize' . I'm assuming this is the event that is triggered when the user form opens ?

I've created a text box and named it 'DisplayMostRecentValue' like yours. My code is as follows :

Sub ufOrdersForm_Initialize()
DisplayMostRecentValue.Caption = Cells(1, 1).End(xlDown).Value
End Sub

I've also attached a couple of screen shots.

Thanks

Attached files
Text-box-screen-shot.jpg
Orders-form-screenshot.jpg

Hi Toco,

well it is not easy without a sample workbook but I try.

First of all, in my sample, I used label box not a text box. In your case, you have to populate not a caption but a value. Additionally, the code for the initialization of the userform needs to be inside the userform and must have the name "UserForm_Initialize".

Also, check in which column you have the values you'd like to display in the dedicated textbox. In my code, I assume they are in column A.

Sub UserForm_Initialize()

DisplayMostRecentValue.Value= Cells(1, 1).End(xlDown).Value

End Sub

Select allOpen in new window

Hi,

I've attached a cut-down version of what I'm trying to do......

When the user clicks on the 'Order Form' button on the Order sheet I want the Order Form to open with details of the most recent order (in this instance, Order no. 8126). This is why I was trying to establish the latest Order Number.

I then want it to highlight the relevant row in the Orders listbox on the right hand side and make the entries in the listbox 'clickable'...or maybe that warrants another Question ?

Hope that makes it clearer.

Thanks
Toco
Example-Orders-application.xlsm

Hi Toco,

great, many thanks for the workbook and further explanation.

It helped a lot.

I have done following things:

All is done by a simple code for initialization of the userform:

Private Sub UserForm_Initialize()

Dim OrdersRange As Range
Dim OrdersArray As Variant

'create range with orders and convert it to array
Set OrdersRange = Range(Cells(3, 1), Cells(1048576, 6).End(xlUp))
OrdersArray = OrdersRange

'populate listbox with orders array
liOrders.List = OrdersArray

'select last item in the listbox (assuming that this is the most recent order)
liOrders.ListIndex = liOrders.ListCount - 1

End Sub

Select allOpen in new window

I am not sure what you mean that you want to make the entries 'clickable'? You mean that upon selection of an item in the listbox, some fields in the left side of the userform should be populated? If yes, which exactly?
Example-Orders-application-johnn.xlsm

Hi,

Yes thanks, the UserForm_Initialize procedure now works well. However, apologies but I'm not sure I explained the requirements fully. Ignoring the Listbox control for the moment (I shall raise a separate question for this later).....

My original plan was when I click on the 'Order form' button on the Orders sheet....

1. The User Form should open with the fields in the left side of the form populated with the values from the most recent order (the bottommost row) of the Orders sheet. The fields are :
- Order no (from the Orders sheet)
- Order date (from the Orders sheet)
- Status (from the Orders sheet)
- Category  (from the Orders sheet)
- Description  (from the Orders sheet)
- Client name  (from the Orders sheet)
- Client Address (VLOOKUP on Clients sheet using lookup value of Client name)
- Town (VLOOKUP on Clients sheet using lookup value of Client name)
- Postcode (VLOOKUP on Clients sheet using lookup value of Client name)
2. This means the user can then use the PREVIOUS and NEXT buttons to display each record in turn. Please ignore the READ button.
3. The 38 Sales and Purchases fields on the User Form will be populated using the Order Number as a key on the Sales and Purchases sheets (I haven't built these sheets yet).

The Listbox was only something I added later as I thought it may be an aid to the user to have a condensed list of all the orders,

Hope I haven't confused you more.

Hi Toco,

yeah, unfortunately not all the requirements were clear previously.

I adjusted the user form based on your last comment:

I added variables for userform:

Dim OrdersArray() As Variant
Dim ClientsArray() As Variant

Select allOpen in new window

and adjusted initialization of the userform:

Private Sub UserForm_Initialize()

'create array with orders
OrdersArray = Range(Cells(3, 1), Cells(1048576, 6).End(xlUp)).Value

'create array with clients
ClientsArray = Range(Sheets("Clients").Cells(3, 2), Sheets("Clients").Cells(Sheets("Clients").Cells(3, 1).End(xlDown), 20)).Value

'populate listbox with array with orders
liOrders.List = OrdersArray

'select last item in the listbox (assuming that this is the most recent order)
liOrders.ListIndex = liOrders.ListCount - 1

End Sub

Select allOpen in new window

and added some code activated upon selection of any item in the listbox:

Private Sub liOrders_Click()

On Error Resume Next

With Application

    'populate order information
    teOrderNo = .VLookup(liOrders.Value, OrdersArray, 1, False)
    teOrderDate = .VLookup(liOrders.Value, OrdersArray, 2, False)
    coStatus = .VLookup(liOrders.Value, OrdersArray, 3, False)
    coCategory = .VLookup(liOrders.Value, OrdersArray, 4, False)
    coDescription = .VLookup(liOrders.Value, OrdersArray, 5, False)
    coClientName = .VLookup(liOrders.Value, OrdersArray, 6, False)
    
    'populate client information
    teClientAddress1 = .VLookup(coClientName.Value, ClientsArray, 6, False)
    teClientAddress2 = .VLookup(coClientName.Value, ClientsArray, 7, False)
    teClientAddress3 = .VLookup(coClientName.Value, ClientsArray, 8, False)
    teClientTown = .VLookup(coClientName.Value, ClientsArray, 9, False)
    teClientPostcode = .VLookup(coClientName.Value, ClientsArray, 11, False)

End With

End Sub

Select allOpen in new window

and also added code for buttons NEXT and PREVIOUS:

'Click PREVIOUS Button
Private Sub cbPrevious_Click()

If liOrders.ListIndex > 0 Then

    liOrders.ListIndex = liOrders.ListIndex - 1

End If

End Sub

'Click NEXT Button
Private Sub cbNext_Click()

If liOrders.ListIndex < liOrders.ListCount - 1 Then

    liOrders.ListIndex = liOrders.ListIndex + 1

End If

End Sub

Select allOpen in new window

Example-Orders-application-johnn.xlsm

Hi John,
Many thanks for introducing some efficient code. I'm learning a lot from this exercise. I like the way you have :
1. Loaded two arrays (Orders and Clients) from their respective sheets to populate the form fields and the listbox.
2. On opening, the User form defaults to the latest record on the Orders sheet.
3. Made the NEXT and PREVIOUS functionality work in sync with the listbox.
4. Added functionality to the listbox so an item that is clicked populates the form fields.

However, the Add and Update functionality no longer work as they should (on account of my inefficient code, no doubt). When updating the Orders sheet with an Add or an Update, the row number is out of sync with the Listbox array. Do I use the ListIndex as the row number for the Orders sheet ? Not sure what to do here.

Also, the purpose of the CLEAR button is so the user can blank out the input form fields (not the Listbox) so they can enter a new record and then click the ADD button. This doesn't work now because the Initialize procedure seems to override the Unload in the cbClear_Click procedure.

Regards
Toco

Hi Toco,

sure, the code implemented by me causes that the other parts of the user form do not work.

Hope you do not mind if I check this in the evening (I am currently about to have a meeting at the customer).

Just to make sure that I'll implement the functionality appropriately:

By the way, would not be better if the user would not have to click on button CLEAR and then ADD to add a new order? How about having just a single button called ADD. Upon click on it, the form will be emptied, cells activated and its label will be changed to SAVE or CONFIRM. Additionally, we can change its color so that it is clear for the user that to add a new order, he has to click on the button.

Just think about it.

Also please make sure that you write down all your recommendations to mitigate the risk of reworking the code/logic as we have experienced at the very beginning ;-)

Thanks.

Regards,
johnnyho_

Hi Johnny,

I really appreciate your help on this. This is as much a learning exercise for me as it is a means to an end. The intention is to automate what were static worksheets, and to learn some VBA on the way.

As for functionality, just to confirm:

ADD - as you stated. Order number is automatically generated (incremented from the value on the Parameters sheet). I've thought about this and I think your suggestion is more logical from a user perspective. So, yes, I like your recommendations when adding a record.
UPDATE - as you stated
DELETE - redundant at the moment
READ - redundant at the moment
CLEAR - as you stated

I've taken on board your comment regarding requirements specification. I assumed a simple example would have sufficed but it seems to have opened up a can of worms.

I think for the additional Listbox functionality I'll raise a new Question on completion of this one, as you have laboured hard for your 500 points !

Thanks,
Toco

Hi Toco,

here we go:

1. I slightly amended the initialization of the userform. Now, the values in the drop-down lists "Status", "Category", "Description" and "Name" are dynamically populated based on the values defined in related columns:

Private Sub UserForm_Initialize()

Dim x As Long

'create array with orders
OrdersArray = Range(Cells(3, 1), Cells(1048576, 6).End(xlUp)).Value

'create array with clients
ClientsArray = Range(Sheets("Clients").Cells(3, 2), Sheets("Clients").Cells(Sheets("Clients").Cells(3, 1).End(xlDown), 20)).Value

'populate listbox with array with orders
liOrders.List = OrdersArray

'select last item in the listbox (assuming that this is the most recent order)
liOrders.ListIndex = liOrders.ListCount - 1

'populate comboboxes "Status", "Category" and "Description"
With Sheets("Form_Lists")

    For x = 4 To .Cells(1048576, 8).End(xlUp).Row
                  
        coStatus.AddItem .Cells(x, 8)

    Next x
    
    For x = 4 To .Cells(1048576, 9).End(xlUp).Row
                  
        coCategory.AddItem .Cells(x, 9)

    Next x

    For x = 4 To .Cells(1048576, 10).End(xlUp).Row
                  
        coDescription.AddItem .Cells(x, 10)

    Next x
    
End With

'populate combobox "Clients"
With Sheets("Clients")

    For x = 3 To .Cells(1048576, 2).End(xlUp).Row
                  
        coClientName.AddItem .Cells(x, 2)

    Next x

End With

End Sub

Select allOpen in new window

2. Upon selection/change of the client name, also the address is updated:

Private Sub coClientName_Change()

On Error Resume Next

With Application

    teClientAddress1 = .VLookup(coClientName.Value, ClientsArray, 6, False)
    teClientAddress2 = .VLookup(coClientName.Value, ClientsArray, 7, False)
    teClientAddress3 = .VLookup(coClientName.Value, ClientsArray, 8, False)
    teClientTown = .VLookup(coClientName.Value, ClientsArray, 9, False)
    teClientPostcode = .VLookup(coClientName.Value, ClientsArray, 11, False)

End With

End Sub

Select allOpen in new window

3. Upon click on "UPDATE" button, the values are stored in the workbook and also the list of orders in the userform is updated accordingly.

Private Sub cbUpdateOrder_Click()

Dim ActiveOrder As Long

ActiveOrder = liOrders.ListIndex + 1

OrdersArray(ActiveOrder, 3) = coStatus
OrdersArray(ActiveOrder, 4) = coCategory
OrdersArray(ActiveOrder, 5) = coDescription
OrdersArray(ActiveOrder, 6) = coClientName

Range(Cells(3, 1), Cells(1048576, 6).End(xlUp)).Value = OrdersArray

liOrders.List = OrdersArray

liOrders.ListIndex = ActiveOrder - 1

MsgBox "Your order has been updated successfully", vbInformation

End Sub

Select allOpen in new window

4. Upon click on the "CLEAR" button, the userform (just order and client data at the moment) is cleaned up and user is asked to enter the data and press "ADD" button. Order no and date of order creation are added to the userform. Additionally, the "ADD" button is enabled as this is disabled by default.

Private Sub cbClear_Click()

'clean up order information and prepare new order
teOrderNo = OrdersArray(liOrders.ListIndex + 1, 1) + 1
teOrderDate = Format(Date, "dd/mm/yyyy")
coStatus.ListIndex = 0
coCategory.ListIndex = 0
coDescription.ListIndex = 0
coClientName = ""
teClientAddress1 = ""
teClientAddress2 = ""
teClientAddress3 = ""
teClientTown = ""
teClientPostcode = ""

cbAddOrder.Enabled = True

MsgBox "Please enter details of new order and then save it by click on ADD button", vbInformation

End Sub

Select allOpen in new window

5. Upon click on the "ADD" button, the new order is added to the workbook and list of orders is updated. Additionally, the "ADD" button is disabled.

Private Sub cbAddOrder_Click()
   
Dim NewOrder As Long

NewOrder = UBound(OrdersArray) + 1

OrdersArray = Application.Transpose(OrdersArray)

ReDim Preserve OrdersArray(1 To UBound(OrdersArray), 1 To NewOrder)

OrdersArray = Application.Transpose(OrdersArray)

OrdersArray(NewOrder, 1) = teOrderNo
OrdersArray(NewOrder, 2) = teOrderDate
OrdersArray(NewOrder, 3) = coStatus
OrdersArray(NewOrder, 4) = coCategory
OrdersArray(NewOrder, 5) = coDescription
OrdersArray(NewOrder, 6) = coClientName

Range(Cells(3, 1), Cells(Cells(1048576, 1).End(xlUp).Row + 1, 6)).Value = OrdersArray

liOrders.List = OrdersArray

liOrders.ListIndex = liOrders.ListCount - 1

cbAddOrder.Enabled = False

MsgBox "New order has been created successfully", vbInformation

End Sub

Select allOpen in new window

Maybe there is something more but I really cannot remember now (it is too late actually).

Please test and report if you see any bug.

The polishing of the code could be done once you agree to the functionality implemented.

Take care.


Regards,
Jan
Example-Orders-application-johnn.xlsm

Hi Jan,

I like the functionality. It's looking really good. However, I did find a small bug when I did the following :

1. Open the Userform
[The most recent order is displayed in the fields, and highlighted in the Listbox]
2. Click the Previous button a few times
[The relevant record is displayed in the fields, and highlighted in the Listbox]
3. Click the Clear button
[The Add message is displayed and the ADD button is enabled]
4. Enter a new record in the fields and click the ADD button
[A new record is created and added to the end of the worksheet range and also the Listbox, as required]

However, it increments the Order number last displayed. So when I clicked the PREVIOUS button back to order 8117 and then did an ADD, it created a record with an order number of 8118, and not 8127 as expected.

Also, the dates in the Listbox are inconsistent. Is this something I can resolve by formatting them when they are in the array ?

Kind regards
Toco

Hi Jan,

Firstly, many thanks for your excellent help and hard work in assisting me in developing my application. I'm very pleased with the outcome and have tested it thoroughly.

Further, my attempts at VBA are usually clumsy and longwinded, but your coding has taught me a great deal how to look at solutions which are not only effective but efficient.

I shall attempt to develop the rest of the user form (Sales and Purchases fields) with that in mind.

Much appreciated.
Good luck and thanks
Phil

Hi Phil,

I am glad that I could help you!

I am not developer, I "learned" VBA by myself couple of years ago in order to be able to help my former department to have tools we would never get or would be expensive if done by professionals.

Feel free to contact me at any time. I am at your disposal.


Regards,
Jan

Woah ! Most impressed.