Hi,
I'm hoping the community take pity on a lowly excel user and offer some solutions to my problem!!!
My work has charged me with the responsibility of creating client profiles that can be accessed at the touch of a button - I am lobbying for a B.I tool, but I need to create something in the interim.
As I am an Intermediate to Advanced excel user I thought VBA would be a good way to attack this project - however I am a VBA newbie and teaching myself using a combination of Google and VBA for Dummies books.
I have turned to the forum as I have hit a bump in the road with my current avenue -
What I need:
I need to create a userform with a list of companies, either divided in to sector or by alphabetised headings (i.e. Finance or A-D). Once the company is selected the corresponding text boxes auto-fill with information about the company and our relationship with them (i.e. past deals, main negotiators etc.)
What I have done so far:
I have attempted to create a treeview userform that lists the companies alphabetically, which when selected; auto fill with the relative information about the company. Within the Workbook I have two sheets - Sheet1 comprises of headings A-D, E-H, I-L, M-P, Q-T, U-X, Y-Z and Num. The cells below are populated with the names of the companies that fall within the aphabetised division.
-Sheet2 then has the company names and the relevant information in alphabetical order.
I have created my userform and started to write the code for excel to perform the task at hand.
The Code:
As the company list on sheet1 and the details on sheet2 are not finite, I want to avoid hard-coding and create a code that reads the last populated cell and then loops through each cell, so that when the source data is updated, those changes filter down in to the userform.
My code currently looks like this -
Private Sub UserForm_Initialize()
Worksheets("Sheet1").Activate
'Parent Nodes
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 1).Value, Text:=Sheet1.Cells(1, 1).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 2).Value, Text:=Sheet1.Cells(1, 2).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 3).Value, Text:=Sheet1.Cells(1, 3).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 4).Value, Text:=Sheet1.Cells(1, 4).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 5).Value, Text:=Sheet1.Cells(1, 5).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 6).Value, Text:=Sheet1.Cells(1, 6).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 7).Value, Text:=Sheet1.Cells(1, 7).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 8).Value, Text:=Sheet1.Cells(1, 8).Value
'Child Nodes
Call FillChildNodes(1, "A-D")
Call FillChildNodes(2, "E-H")
Call FillChildNodes(3, "I-L")
Call FillChildNodes(4, "M-P")
Call FillChildNodes(5, "Q-T")
Call FillChildNodes(6, "U-X")
Call FillChildNodes(7, "Y-Z")
Call FillChildNodes(8, "Num")
End Sub
Sub FillChildNodes(ByVal col As Integer, ByVal heading As String)
'Get the last row with data in it for Column col
Dim lastRow As Long
With Sheet1
lastRow = .Cells(.Rows.Count, col).End(x1Up).Row
End With
Dim counter As Integer
counter = 1
'Loop round and add child nodes
For Each Company In Range(Cells(2, col), Cells(lastRow, col))
TreeView1.Nodes.Add Sheet1.Cells(1, col).Value, tvwChild, heading + CStr(counter), Title
counter = counter + 1
Next company
End Sub
I received the error - compile error: Variable Required - can't assign this to an expression. I've used F8 and the issue appears to lie here -
Sub FillChildNodes(ByVal col As Integer, ByVal heading As String)
So evidently Excel doesn't like the variable 'heading" and will probably dislike the variable "company" also - but I am at a loss as to know what to call the headings and company names beneath (which should populate the parent and child nodes).
I have tried changing them to a number of different variable names but I then receive run-time error 1004.
Can anyone provide any advice as to how to name the variable OR suggest an easier way to achieve what I'm aiming for (as I think I may be over complicating things).
If you decide to help me with the code can I ask that you break down your explanation as I'm a VBA beginner.
If you offer a different approach; I won't need step by step instructions, just a general pointer and I'm sure I can navigate my way from there.
Any and all help will be much appreciated.
Thanks in advance.
I'm hoping the community take pity on a lowly excel user and offer some solutions to my problem!!!
My work has charged me with the responsibility of creating client profiles that can be accessed at the touch of a button - I am lobbying for a B.I tool, but I need to create something in the interim.
As I am an Intermediate to Advanced excel user I thought VBA would be a good way to attack this project - however I am a VBA newbie and teaching myself using a combination of Google and VBA for Dummies books.
I have turned to the forum as I have hit a bump in the road with my current avenue -
What I need:
I need to create a userform with a list of companies, either divided in to sector or by alphabetised headings (i.e. Finance or A-D). Once the company is selected the corresponding text boxes auto-fill with information about the company and our relationship with them (i.e. past deals, main negotiators etc.)
What I have done so far:
I have attempted to create a treeview userform that lists the companies alphabetically, which when selected; auto fill with the relative information about the company. Within the Workbook I have two sheets - Sheet1 comprises of headings A-D, E-H, I-L, M-P, Q-T, U-X, Y-Z and Num. The cells below are populated with the names of the companies that fall within the aphabetised division.
-Sheet2 then has the company names and the relevant information in alphabetical order.
I have created my userform and started to write the code for excel to perform the task at hand.
The Code:
As the company list on sheet1 and the details on sheet2 are not finite, I want to avoid hard-coding and create a code that reads the last populated cell and then loops through each cell, so that when the source data is updated, those changes filter down in to the userform.
My code currently looks like this -
Private Sub UserForm_Initialize()
Worksheets("Sheet1").Activate
'Parent Nodes
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 1).Value, Text:=Sheet1.Cells(1, 1).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 2).Value, Text:=Sheet1.Cells(1, 2).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 3).Value, Text:=Sheet1.Cells(1, 3).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 4).Value, Text:=Sheet1.Cells(1, 4).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 5).Value, Text:=Sheet1.Cells(1, 5).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 6).Value, Text:=Sheet1.Cells(1, 6).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 7).Value, Text:=Sheet1.Cells(1, 7).Value
TreeView1.Nodes.Add Key:=Sheet1.Cells(1, 8).Value, Text:=Sheet1.Cells(1, 8).Value
'Child Nodes
Call FillChildNodes(1, "A-D")
Call FillChildNodes(2, "E-H")
Call FillChildNodes(3, "I-L")
Call FillChildNodes(4, "M-P")
Call FillChildNodes(5, "Q-T")
Call FillChildNodes(6, "U-X")
Call FillChildNodes(7, "Y-Z")
Call FillChildNodes(8, "Num")
End Sub
Sub FillChildNodes(ByVal col As Integer, ByVal heading As String)
'Get the last row with data in it for Column col
Dim lastRow As Long
With Sheet1
lastRow = .Cells(.Rows.Count, col).End(x1Up).Row
End With
Dim counter As Integer
counter = 1
'Loop round and add child nodes
For Each Company In Range(Cells(2, col), Cells(lastRow, col))
TreeView1.Nodes.Add Sheet1.Cells(1, col).Value, tvwChild, heading + CStr(counter), Title
counter = counter + 1
Next company
End Sub
I received the error - compile error: Variable Required - can't assign this to an expression. I've used F8 and the issue appears to lie here -
Sub FillChildNodes(ByVal col As Integer, ByVal heading As String)
So evidently Excel doesn't like the variable 'heading" and will probably dislike the variable "company" also - but I am at a loss as to know what to call the headings and company names beneath (which should populate the parent and child nodes).
I have tried changing them to a number of different variable names but I then receive run-time error 1004.
Can anyone provide any advice as to how to name the variable OR suggest an easier way to achieve what I'm aiming for (as I think I may be over complicating things).
If you decide to help me with the code can I ask that you break down your explanation as I'm a VBA beginner.
If you offer a different approach; I won't need step by step instructions, just a general pointer and I'm sure I can navigate my way from there.
Any and all help will be much appreciated.
Thanks in advance.
