more forms questions...

[Chris Salcedo]

I have the following code to initialize a form that gets all the named
ranges in a workbook.

Private Sub UserForm_Initialize()
SourceTextBox.SetFocus
For Each nName In Names
Me.LeftBox.AddItem (nName.Name)
Next nName
End Sub

This works great but what I want to do now is initialize the form
putting the cursor in the SourceTextBox then taking as input from the
textbox the name of the sheet from where I want to get the named ranges
only from that sheet something like this...

Private Sub UserForm_Initialize()
SourceTextBox.SetFocus
End Sub

I get a Form that is empty and the cursor in the correct place.
I enter the name of the sheet I am interested in -- "data"
( goto the sheet read the named ranges of that sheet then insert them
in the correct list box then wait for the rest of the button acctions
(this code is already done)) something like this:

Private sub SourceTextBox_AfterUpdate()'entered "data" then enter

For Each nName In What do I put here ????
Me.LeftBox.AddItem (nName.What do I put here ???)
Next nName
end sub


Thanks

Chris

 

[Rowan Drummond]

Hi Chris

Try:

Private Sub SourceTextBox_AfterUpdate()
Dim nName As Name
For Each nName In ThisWorkbook.Names
If nName.RefersToRange.Parent.Name = Me.SourceTextBox.Text Then
Me.LeftBox.AddItem nName.Name
End If
Next nName
End Sub

Hope this helps
Rowan