Passing arguments with worksheet_change to dynamically update ranges

[Prevost]

Hi There. I have found a sub that will dynamically update named ranges in worksheets. There are two things that I would like to know if possible. First, I would like to know if there is a way to disregard blank cells in that range (starting at A3 that goes to the last row). I am using that range for a combobox and would prefer it if there were not any blank selections in the list. Second, I have multiple ranges and I do not want to call this sub everytime, so is there a way to create this macro with variables to insert? I am still not 100% clear on passing arguments and am trying to learn more. I thought something like

Private Sub worksheet_change(ByVal target as Range, Column as String, FirstCell as String, RangeName as String)

and then filling in the values for Column, the starting cell and the name of the range....

Thanks!

Private Sub worksheet_change(ByVal target As Range)
If Intersect(target, Columns("A:A")) Is Nothing Then Exit Sub
Dim lRow As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A3:A" & lRow).Name = "MyRange"
End Sub

 

[Gavin T]

You can create a dynamic named range without using VBA. When you create the named range in the "Refers To:" box enter this formula:

=OFFSET(A3,0,0,MATCH("*",A:A,-1),1)

 

[Prevost]

Thanks Gavin T. That will work for me. Do you know how to solve it using VBA ?

 

[Gavin T]

Try this:

    ActiveWorkbook.Names.Add Name:="NamedRange", RefersToR1C1:="=OFFSET(A3,0,0,MATCH("*",A:A,-1),1)"

Good Luck.

 

[Prevost]

Hi Gavin. Sorry but I was not clear with my question and I may have to start a new thread to clarify myself. I was not sure what the (Byval target as Range) meant and was confused as to how to pass arguments into that procedure. I now know that by adding the Byval in front of the range variable target, it is using a copy of the target variable and does not actually change that variable. By just entering (target as Range), the variable is now altered within that procedure. So that clarified things for me. My real question (that I didn't ask because I was unsure about the previous information) was how to get a variable into the column range in the intersect function.

I would like to know how to assign variables into the Intersect(target, Columns(ColumnVariable:ColumnVariable)). In the case below, the column is column A in this case. I hope this is more clear.


If Intersect(target, Columns("A:A")) Is Nothing Then Exit Sub

Thanks!

 

[Prevost]

And to further complicate things, just changing the variable type in the procedure parenthesis on the worksheet_change(Byval target as Range) to worksheet_change(target as Range) did not work...

 

[Norie]

Where does the column variable come from?

Is it related to the cell that has changed, ie Target?

By the way, why do you need these named ranges?

Couldn't you populate the combobox(es) without them?

 

[Prevost]

Hi Norie. The column variable is given by me. I have a worksheet that I am using to maintain a list of drop down menus for some combo boxes. So depending on how many lists I have, I will have that many column variables. It is a variable that I will pass as an argument into the worksheet_change procedure. The Target variable is part of that worksheet_change procedure and that is what we are looking for to see if that target has changed. I need these named ranges to maintain the lists for drop down menus in my userform. I could populate the combo boxes without them, but I do not want to have to manually track the named lists. If someone makes a change to the list, I want the drop down menu to update (by means of the worksheet_change procedure) to reflect those changes.

I want to find a way to basically pass arguments into the procedure below, so I do not have to type this entire length of code for every named range I have.

Private Sub worksheet_change(ByVal target As Range)     If Intersect(target, Columns("A:A")) Is Nothing Then Exit Sub         Dim lRow As Integer         lRow = Range("A" & Rows.Count).End(xlUp).Row         Range("A3:A" & lRow).Name = "MyRange" End Sub</pre>

 

[Norie]

You can't pass your own arguments to the Change (or any other) event.

When are you populating the comboboxes on the userform?

How are you displaying the userform modally?

Also when would the user be changing the data in the lists?

 

[Prevost]

So I cannot pass my arguments into the worksheet_change event? Well if I cannot do that then I cannot accomplish my initial question and I may have to revise what it is I want to do.

Also, what do you mean by "(or any other) event"? So I cannot pass any arguments to (for example) a Workbook_Open() event? Not that I would or can think of why I would need to.....

Thanks.