Using Named Ranges in VBA with Userforms

DaveUK

Board Regular
Joined
Jan 24, 2005
Messages
245
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,611
Office Version
  1. 2016
Platform
  1. Windows
Here is a way of doing it :

Place this in the UserForm Module :

Code:
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

Board Regular
Joined
Jan 24, 2005
Messages
245
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

Board Regular
Joined
Jan 24, 2005
Messages
245
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.

Code:
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
 
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,163,991
Messages
5,834,764
Members
430,319
Latest member
Excelhelppll

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