Hi
This is my first post on this forum and I know that you will be able to help me as from my searches I can see that this has been asked and answered
before (but I couldn't quite understand the answers). I am actually very close to fixing this myself but being someone who has only discovered VBA
in the last few weeks I am finding the learning curve pretty steep.
Anyway, using a tutorial I found on fontstuff.com, I am creating a user form which I want my team members to complete.
The data entered should feed into an excel sheet for analysis.
As you can see from the first screen shot I have created a fairly large user form and have now begun to work my way down through the code.
Most fields will be simple numerical or text fields but there are some Comboboxes I want to control.
My first problem is trying to get one combo box to be dependent on another.
I have practiced with a simple version using Andrew Poulsom’s advice in this thread and got it to work fine.
I have also read the other threads linked in that thread.
However when I try to copy the VB code into my form, something is not quite right.
The problem details are as follows:
I want to select the Portfolio Manager from the first Combobox. This Combobox is named cboPortfolioManager
The list of portfolio managers is on a dynamic range named Portfolio_Manager. (See excel screen below – column A)
This part works fine.
The issue is when I try to select the Company Name from the second Combobox, it remains blank. I am not getting any error messages.
Just nothing appearing in the drop down menu.
This second Combobox is named cboCompanyName. The company names are also on dynamic ranges, named after their relationship manager.
Each of these ranges has exactly the same name as selected from the first Combobox so for the screen shot above, the portfolio manager is
AH_POL and I therefore want the company name Combobox to list the companies managed by AH_POL. (see excel screen below – Column C)
Below is a screen shot showing the layout of my dynamic ranges.
There are c.30 columns with each containing between 5 and 20 companies.
As you will understand I have whitened out the font for two columns and
Amended the actual company names in the dynamic range AH_POL
Some basics
I am using Windows XP Pro and Excel 2003
Below is the code I have in my VB window
You can see from the ‘comments that I have been trying to eliminate the issue one line at a time but am now turning to you guys.
Private Sub UserForm_Activate()
cboPortfolioManager.RowSource = "Portfolio_Manager"
End Sub
Private Sub cboCompanyName_Change()
cboCompanyName.RowSource = cboPortfolioManager.Value
End Sub
Private Sub cboPortfolioManager_Change()
'cboPortfolioManager.RowSource = "Portfolio_Manager"
'cboCompanyName.RowSource = cboPortfolioManager.Value
End Sub
If you managed to read all the way down to here, THANK YOU.
This is my first post on this forum and I know that you will be able to help me as from my searches I can see that this has been asked and answered
before (but I couldn't quite understand the answers). I am actually very close to fixing this myself but being someone who has only discovered VBA
in the last few weeks I am finding the learning curve pretty steep.
Anyway, using a tutorial I found on fontstuff.com, I am creating a user form which I want my team members to complete.
The data entered should feed into an excel sheet for analysis.
As you can see from the first screen shot I have created a fairly large user form and have now begun to work my way down through the code.
Most fields will be simple numerical or text fields but there are some Comboboxes I want to control.
My first problem is trying to get one combo box to be dependent on another.
I have practiced with a simple version using Andrew Poulsom’s advice in this thread and got it to work fine.
I have also read the other threads linked in that thread.
However when I try to copy the VB code into my form, something is not quite right.
The problem details are as follows:
I want to select the Portfolio Manager from the first Combobox. This Combobox is named cboPortfolioManager
The list of portfolio managers is on a dynamic range named Portfolio_Manager. (See excel screen below – column A)
This part works fine.
The issue is when I try to select the Company Name from the second Combobox, it remains blank. I am not getting any error messages.
Just nothing appearing in the drop down menu.
This second Combobox is named cboCompanyName. The company names are also on dynamic ranges, named after their relationship manager.
Each of these ranges has exactly the same name as selected from the first Combobox so for the screen shot above, the portfolio manager is
AH_POL and I therefore want the company name Combobox to list the companies managed by AH_POL. (see excel screen below – Column C)
Below is a screen shot showing the layout of my dynamic ranges.
There are c.30 columns with each containing between 5 and 20 companies.
As you will understand I have whitened out the font for two columns and
Amended the actual company names in the dynamic range AH_POL
Some basics
I am using Windows XP Pro and Excel 2003
Below is the code I have in my VB window
You can see from the ‘comments that I have been trying to eliminate the issue one line at a time but am now turning to you guys.
Private Sub UserForm_Activate()
cboPortfolioManager.RowSource = "Portfolio_Manager"
End Sub
Private Sub cboCompanyName_Change()
cboCompanyName.RowSource = cboPortfolioManager.Value
End Sub
Private Sub cboPortfolioManager_Change()
'cboPortfolioManager.RowSource = "Portfolio_Manager"
'cboCompanyName.RowSource = cboPortfolioManager.Value
End Sub
If you managed to read all the way down to here, THANK YOU.
