Using Named Ranges in VBA with Userforms

[DaveUK]

Please advise:

I need to name a range on Worksheet "Schedules" running from B3 to the last non empty cell in the B column.

I want the named rnage to be "NRWorkOrderNumber".

Question 1: How do i code this in VBA?

I have a Userform which will display the "NRWorkOrderNumber" named range in a Combobox (named "cbListWorkOrderNumbers"), which can select whichever value i need. I know to insert the value NRWorkOrderNumber in the RowSource field.

Also i have a CommandButton which is called "cbDeleteWorkOrderNumber", which when clicked will delete the entry from the named range. Obviously then i need to rename the "NRWorkOrderNumber" named range to not include the deleted value and to refresh the Combobox "cbListWorkOrderNumbers" to refelct the change.

Question 2: Is this a Dynamic Named Range?

Question 3: How do i code this in VBA?

Many thanks for any help.

 

[tusharm]

You can put together the code yourself. Just build the pieces with the macro recorder. If you are still stuck, post the code you have created and someone should be able to help you put it together.

To find the last cell in a column, turn on the macro recorder (Tools | Macro > Record new macro...), use the keyboard to find the last cell, and turn off the recorder. XL will give you the necessary code.

To create a range from the first cell to the last cell, check the XL VBA help for the 'Range Collection' (w/o the quotes). It tells you have to build a range given the first and the last cell in the range.

You can get most of the other code in a similar fashion.

 

[Jaafar Tribak]

Here is a way of doing it :

Place this in the UserForm Module :

Dim objRowSource As Range
Dim objCell As Range


Private Sub cbDeleteWorkOrderNumber_Click() 'CommandButton

    'find & clear the cell whose value is picked by combobox
    objRowSource.Find(cbListWorkOrderNumbers).ClearContents
    cbListWorkOrderNumbers.Clear
    'Update the combobox
    Populate_List

End Sub

Private Sub UserForm_Initialize()

    Populate_List

End Sub

Sub Populate_List()

    'Define the range from B3 to last non empty cell in column B
    Set objRowSource = Range(Cells(3, 2), Cells(Rows.Count, 2).End(xlUp))
    ' Add non empty cells values to combobox
    For Each objCell In objRowSource.Cells
        If Not IsEmpty(objCell) Then
            cbListWorkOrderNumbers.AddItem objCell
        End If
    Next
    'Show first item on combo
    cbListWorkOrderNumbers.ListIndex = 0

End Sub

As you may have noticed , I didn't need to use a Named Range to achieve the requiered results.However I have used the same names you gave to the ComboBox and CommandButton on the UserForm.


Regards.

 

[DaveUK]

Many thanks !!

However deleting the workordernumber just deletes the Cell and not the row.

I need to delete the row in which the deleted workordernumber is in so it does not appear in the combobox list again..

Please Help!!

 

[DaveUK]

I have found the solution !!

Many thanks for the code - complicated for me to understand but will try my best.

I just made these changes and works perfectly now.

Private Sub cbDeleteWorkOrderNumber_Click()
    'find & clear the cell whose value is picked by combobox
    objRowSource.Find(cbListWorkOrderNumbers).EntireRow.Delete
    cbListWorkOrderNumbers.Clear
    'Update the combobox
    Populate_List
End Sub