PDA

View Full Version : [SOLVED] Help using Named Range in VBA tp pupultate a worksheet.



mykal66
10-24-2014, 01:47 AM
Hi everyone - I'm looking for help using named ranges in VBA to populate data in a worksheet if anyone can help please?

I've attached an example of what i am trying to get to work.

Sheet3 literally takes the names of the separate teams on sheet2 to make a named range 'TEAMS'

Sheet2 will contain the separate team info lists and each will have a named range to match their team name e.g. Team1 range A3:B5 is named Team1

On Sheet1 users would use the button and pop up box to select which team information they want to see. The name of the team they have selected shows in cell G4 on sheet 1. This is where i get stuck; i need the code to use the name in G4 and then display the same named range underneaths starting in G5.

As always, help or pointers is appreciated.

Mykal

12433

Kenneth Hobs
10-24-2014, 06:55 AM
Private Sub CommandButton1_Click() Dim r As Range
With Worksheets("Sheet1")
Set r = Range(CBO1.Value)
.Range("G4").Value = CBO1.Value
.Range("G5").Resize(r.Rows.Count, r.Columns.Count).Value = Range(CBO1.Value).Value
End With
Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim cpro As Range
Dim cAdd As Range
Dim cFin As Range
Dim ws As Worksheet
Set ws = Worksheets("Sheet3")
' set the range of cells to to populate drop down list and process name attached to that range
For Each cpro In ws.Range("Teams")
With Me.CBO1
.AddItem cpro.Value
.List(.ListCount - 1, 1) = cpro.Offset(0, 1).Value
End With
Next cpro
CBO1.ListIndex = 0
End Sub

mykal66
10-24-2014, 06:31 PM
Hi Kenneth

Thank you very much - really appreciated and works perfect

Mykal

mykal66
10-24-2014, 11:35 PM
[QUOTE=Kenneth Hobs;316906][CODE]Private Sub CommandButton1_Click() Dim r As Range
With Worksheets("Sheet1")
Set r = Range(CBO1.Value)
.Range("G4").Value = CBO1.Value
.Range("G5").Resize(r.Rows.Count, r.Columns.Count).Value = Range(CBO1.Value).Value
End With
Unload Me
End Sub

Hi again Kenneth

I've had another thought and wondered if you are able to help please? Instead of using populating the worksheet is it possible to populate a label on a user form with the range data? I would use the initial form on the example I attached but then add a label e.g. lbl1.

I have been on various forums and tried to adapt the code you gave me e.g. still populate a cell with the selection, use that as the name of the range I want and then tried get that range data into the label but unable to get it to work.


Private Sub CommandButton1_Click()
Dim r As Range
With Worksheets("Sheet1")
Set r = Range(CBO1.Value)
.Range("G4Value = CBO1.Value
Directroy.lbl1.Caption = Range(CBO1.Value).Value
End With
Unload Me
End Sub

Thank you again for your help with original query

Best wishes