I am struggling with range naming where the name is to be the text value of a particular cell.
Briefly: A userform
listbox places names in a column in Sheet2, starting with cell E1 and subsequent transfers from the listbox (which is
multiselect) going to F1, G1 etc. There will be between 6 and 15 names in each case.
A seperate textbox is used to
assign a number to each multiselection, this number is added to "Div" and placed in row 22 of the column to which the
multiselection is transfered.
The text in this cell is used for other things later but I also want to use it to
Name the range to which it refers. This name is then immediately used to populate another listbox in the userform, and will
be used repeatedly later on in the project.
Here is the relavant snip of code that I have been trying to get to
work but I get 'object required' for the ActiveWorkbook.Names.Add.......... line. I did manage to get rid of that once (can't
recall how!!) but then the ListBox2,RowSource gave an error.
Set DvNm = Sheet2.Range("IV1").End(xlToLeft).Offset(21, 0)
Set Rngc = Sheet2.Range("IV1").End(xlToLeft)
Set Rngp = Sheet2.Range("D65536").End(xlUp).Offset(1, 0)
DvNm.Value = "Div" & TextBox1.Value
Range(Rngc, Rngc.Offset(15, 0)).Copy
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:=DvMn.Value, RefersToR1C1:="Range(Rngc, Rngc.Offset(15, 0))"
Application.ScreenUpdating = True
ListBox2.RowSource = DvMn.Value
TextBox1.Value = ""
ListBox1.Enabled = False
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I think it may be a matter of placing quotation marks in the right place but I cannot get it right.
somebody can help.