Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform combo box
Hi,
Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform combo box
I depends on what you think the normal rules are.
right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform combo box
Thank you Tom!
Thats perfect Thanks "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Tom,
Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Souny,
Try using the Change event rather than the click event. The initial recalc seems to be firing the click event code, but should not fire the change event. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Bernie,
Thanks for your response. I tried with Change event, and the problem still exists. I don't know why. Please help. "Bernie Deitrick" wrote: Souny, Try using the Change event rather than the click event. The initial recalc seems to be firing the click event code, but should not fire the change event. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Does the combobox list range contain formulas rather than values?
At the end of it all, you could change your macro code to look at some other enabling value that you store in a named cell somewhere... for example If Range("DisableCombo").Value < "Enabled" Then Exit Sub HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, Thanks for your response. I tried with Change event, and the problem still exists. I don't know why. Please help. "Bernie Deitrick" wrote: Souny, Try using the Change event rather than the click event. The initial recalc seems to be firing the click event code, but should not fire the change event. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Bernie,
Thank you very much for continuing to help. The values in the combo box are looking up the cells $C$1:$C$4 in Sheet1, and the cells are referenced in ListFillRange as $C$1:$C$4 in the combo box Properties. Therefore, the values are not populated from the code. Do you think I need to add your code as part of my code like the following? If Range("$C$1:$C$4").Value < "Enabled" Then Exit Sub Please help. Thanks. "Bernie Deitrick" wrote: Does the combobox list range contain formulas rather than values? At the end of it all, you could change your macro code to look at some other enabling value that you store in a named cell somewhere... for example If Range("DisableCombo").Value < "Enabled" Then Exit Sub HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, Thanks for your response. I tried with Change event, and the problem still exists. I don't know why. Please help. "Bernie Deitrick" wrote: Souny, Try using the Change event rather than the click event. The initial recalc seems to be firing the click event code, but should not fire the change event. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Souny,
I was not able to replicate your problem, with my setup. Are there formulas in C1:C4 or are they values? And if you want, you can send the workbook to me privately. Just make the obvious changes to my email address. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, Thank you very much for continuing to help. The values in the combo box are looking up the cells $C$1:$C$4 in Sheet1, and the cells are referenced in ListFillRange as $C$1:$C$4 in the combo box Properties. Therefore, the values are not populated from the code. Do you think I need to add your code as part of my code like the following? If Range("$C$1:$C$4").Value < "Enabled" Then Exit Sub Please help. Thanks. "Bernie Deitrick" wrote: Does the combobox list range contain formulas rather than values? At the end of it all, you could change your macro code to look at some other enabling value that you store in a named cell somewhere... for example If Range("DisableCombo").Value < "Enabled" Then Exit Sub HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, Thanks for your response. I tried with Change event, and the problem still exists. I don't know why. Please help. "Bernie Deitrick" wrote: Souny, Try using the Change event rather than the click event. The initial recalc seems to be firing the click event code, but should not fire the change event. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Bernie,
I would love to send you my workbook; however, I can't due to the company's policy. Please help me without my workbook. Besides my workbook, I did a test workbook. In the test workbook, I create a combo box in Sheet1, and in cells $C$1:$C$4 of Sheet1, I type in the values. The values in cells $C$1:$C$4 of both actual and test workbooks are values, not formula. Then I have $C$1:$C$4 in ListFillRange and combo box name cboTest in Name of combo box Properties, and I change nothing else in the combo box Properties. In the VBAProject of Sheet1, I have the following code: private sub cboTest_Click() application.screenupdating=false select case cboTest.value case "Selection1" msgbox "selection1" case "selection2" msgbox "selection2" case "selection3" msgbox "selection3" end select application.screenupdating=true end sub I click on the combo box to change the value, and it works. When I close the workbook and reopen it, the combo box code executes. For example, when I open the workbook with "selection3" in my combo box, I would receive a message "selection3". Did I miss something? Is there a setting in Excel (e.g. Tools Options) that I need to check to say do not execute the combo box code when the file is opened? Thanks. "Bernie Deitrick" wrote: Souny, I was not able to replicate your problem, with my setup. Are there formulas in C1:C4 or are they values? And if you want, you can send the workbook to me privately. Just make the obvious changes to my email address. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, Thank you very much for continuing to help. The values in the combo box are looking up the cells $C$1:$C$4 in Sheet1, and the cells are referenced in ListFillRange as $C$1:$C$4 in the combo box Properties. Therefore, the values are not populated from the code. Do you think I need to add your code as part of my code like the following? If Range("$C$1:$C$4").Value < "Enabled" Then Exit Sub Please help. Thanks. "Bernie Deitrick" wrote: Does the combobox list range contain formulas rather than values? At the end of it all, you could change your macro code to look at some other enabling value that you store in a named cell somewhere... for example If Range("DisableCombo").Value < "Enabled" Then Exit Sub HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, Thanks for your response. I tried with Change event, and the problem still exists. I don't know why. Please help. "Bernie Deitrick" wrote: Souny, Try using the Change event rather than the click event. The initial recalc seems to be firing the click event code, but should not fire the change event. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Souny,
I did exactly that before, and again just now, and I do not replicate your problem. Try starting Excel in safe mode. See http://www.cpearson.com/excel/StartupErrors.aspx for instructions on how. Also, I am running Excel 2003 - what version and operating system are you using? Do you have any event code in your default workbook's Thisworkbook's codemodule? HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, I would love to send you my workbook; however, I can't due to the company's policy. Please help me without my workbook. Besides my workbook, I did a test workbook. In the test workbook, I create a combo box in Sheet1, and in cells $C$1:$C$4 of Sheet1, I type in the values. The values in cells $C$1:$C$4 of both actual and test workbooks are values, not formula. Then I have $C$1:$C$4 in ListFillRange and combo box name cboTest in Name of combo box Properties, and I change nothing else in the combo box Properties. In the VBAProject of Sheet1, I have the following code: private sub cboTest_Click() application.screenupdating=false select case cboTest.value case "Selection1" msgbox "selection1" case "selection2" msgbox "selection2" case "selection3" msgbox "selection3" end select application.screenupdating=true end sub I click on the combo box to change the value, and it works. When I close the workbook and reopen it, the combo box code executes. For example, when I open the workbook with "selection3" in my combo box, I would receive a message "selection3". Did I miss something? Is there a setting in Excel (e.g. Tools Options) that I need to check to say do not execute the combo box code when the file is opened? Thanks. "Bernie Deitrick" wrote: Souny, I was not able to replicate your problem, with my setup. Are there formulas in C1:C4 or are they values? And if you want, you can send the workbook to me privately. Just make the obvious changes to my email address. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, Thank you very much for continuing to help. The values in the combo box are looking up the cells $C$1:$C$4 in Sheet1, and the cells are referenced in ListFillRange as $C$1:$C$4 in the combo box Properties. Therefore, the values are not populated from the code. Do you think I need to add your code as part of my code like the following? If Range("$C$1:$C$4").Value < "Enabled" Then Exit Sub Please help. Thanks. "Bernie Deitrick" wrote: Does the combobox list range contain formulas rather than values? At the end of it all, you could change your macro code to look at some other enabling value that you store in a named cell somewhere... for example If Range("DisableCombo").Value < "Enabled" Then Exit Sub HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, Thanks for your response. I tried with Change event, and the problem still exists. I don't know why. Please help. "Bernie Deitrick" wrote: Souny, Try using the Change event rather than the click event. The initial recalc seems to be firing the click event code, but should not fire the change event. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
You may want to just bite the bullet and drop the linkedcell and rowsource and
replace them with code. Souny wrote: Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Bernie,
I am using Excel 2003 Standard and Windows XP Professional. I don't have anything in the ThisWorkbook. What is it supposed to happen if I open my workbook in safemode? Thanks. "Bernie Deitrick" wrote: Souny, I did exactly that before, and again just now, and I do not replicate your problem. Try starting Excel in safe mode. See http://www.cpearson.com/excel/StartupErrors.aspx for instructions on how. Also, I am running Excel 2003 - what version and operating system are you using? Do you have any event code in your default workbook's Thisworkbook's codemodule? HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, I would love to send you my workbook; however, I can't due to the company's policy. Please help me without my workbook. Besides my workbook, I did a test workbook. In the test workbook, I create a combo box in Sheet1, and in cells $C$1:$C$4 of Sheet1, I type in the values. The values in cells $C$1:$C$4 of both actual and test workbooks are values, not formula. Then I have $C$1:$C$4 in ListFillRange and combo box name cboTest in Name of combo box Properties, and I change nothing else in the combo box Properties. In the VBAProject of Sheet1, I have the following code: private sub cboTest_Click() application.screenupdating=false select case cboTest.value case "Selection1" msgbox "selection1" case "selection2" msgbox "selection2" case "selection3" msgbox "selection3" end select application.screenupdating=true end sub I click on the combo box to change the value, and it works. When I close the workbook and reopen it, the combo box code executes. For example, when I open the workbook with "selection3" in my combo box, I would receive a message "selection3". Did I miss something? Is there a setting in Excel (e.g. Tools Options) that I need to check to say do not execute the combo box code when the file is opened? Thanks. "Bernie Deitrick" wrote: Souny, I was not able to replicate your problem, with my setup. Are there formulas in C1:C4 or are they values? And if you want, you can send the workbook to me privately. Just make the obvious changes to my email address. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, Thank you very much for continuing to help. The values in the combo box are looking up the cells $C$1:$C$4 in Sheet1, and the cells are referenced in ListFillRange as $C$1:$C$4 in the combo box Properties. Therefore, the values are not populated from the code. Do you think I need to add your code as part of my code like the following? If Range("$C$1:$C$4").Value < "Enabled" Then Exit Sub Please help. Thanks. "Bernie Deitrick" wrote: Does the combobox list range contain formulas rather than values? At the end of it all, you could change your macro code to look at some other enabling value that you store in a named cell somewhere... for example If Range("DisableCombo").Value < "Enabled" Then Exit Sub HTH, Bernie MS Excel MVP "Souny" wrote in message ... Bernie, Thanks for your response. I tried with Change event, and the problem still exists. I don't know why. Please help. "Bernie Deitrick" wrote: Souny, Try using the Change event rather than the click event. The initial recalc seems to be firing the click event code, but should not fire the change event. HTH, Bernie MS Excel MVP "Souny" wrote in message ... Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Dave,
Thanks for your response. Could you help me with the code? "Dave Peterson" wrote: You may want to just bite the bullet and drop the linkedcell and rowsource and replace them with code. Souny wrote: Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Dave,
Thank you very much for the code. I will try it and let you know. One thing that does not make sense to me. Why do we have to go through the code to populate the values, and why my setup would not work? I thought the intend of the combo box is to execute the code when we click on the combo box and when the value changes in the combo box, not to execute automatically when the file opens. Am I correct? Did I miss something during the setup that you can think of? Thanks again. "Dave Peterson" wrote: Say you have the list in a range on a separate sheet named "ListInfo" in A1:A10. And you want the linked cell on the same worksheet as the combobox in A1. And you want to populate the combobox when the workbook opens. My combobox was named combobox1 and is on Sheet1. I had this code in a General module: Option Explicit Sub Auto_Open() Dim wks As Worksheet Dim ListRng As Range Set wks = Worksheets("Sheet1") With Worksheets("ListInfo") Set ListRng = .Range("A1:A5") End With wks.OLEObjects("combobox1").Object.List = ListRng.Value End Sub Behind the worksheet with the combobox: Option Explicit Private Sub ComboBox1_Change() Me.Range("A1").Value = Me.ComboBox1.Value End Sub ======== I have no idea how often that combobox needs to have its list refreshed. Maybe using a different event would make more sense. Souny wrote: Dave, Thanks for your response. Could you help me with the code? "Dave Peterson" wrote: You may want to just bite the bullet and drop the linkedcell and rowsource and replace them with code. Souny wrote: Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Somethings cause xl to recalculate and think that that linked cell (or list)
changed (I'm not sure which one). I'm not sure what they are or when they do it (what version of excel are you using?). Just a couple of days ago, there was another user who had the same problem. I couldn't duplicate it xl2003 (which is what they were using, too(!)). All I know is that sometimes it's just easier to use code than to struggle. Souny wrote: Dave, Thank you very much for the code. I will try it and let you know. One thing that does not make sense to me. Why do we have to go through the code to populate the values, and why my setup would not work? I thought the intend of the combo box is to execute the code when we click on the combo box and when the value changes in the combo box, not to execute automatically when the file opens. Am I correct? Did I miss something during the setup that you can think of? Thanks again. "Dave Peterson" wrote: Say you have the list in a range on a separate sheet named "ListInfo" in A1:A10. And you want the linked cell on the same worksheet as the combobox in A1. And you want to populate the combobox when the workbook opens. My combobox was named combobox1 and is on Sheet1. I had this code in a General module: Option Explicit Sub Auto_Open() Dim wks As Worksheet Dim ListRng As Range Set wks = Worksheets("Sheet1") With Worksheets("ListInfo") Set ListRng = .Range("A1:A5") End With wks.OLEObjects("combobox1").Object.List = ListRng.Value End Sub Behind the worksheet with the combobox: Option Explicit Private Sub ComboBox1_Change() Me.Range("A1").Value = Me.ComboBox1.Value End Sub ======== I have no idea how often that combobox needs to have its list refreshed. Maybe using a different event would make more sense. Souny wrote: Dave, Thanks for your response. Could you help me with the code? "Dave Peterson" wrote: You may want to just bite the bullet and drop the linkedcell and rowsource and replace them with code. Souny wrote: Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Dave,
Thanks for the explanation. I am using Excel 2003 Standard and Windows XP Profession. Have a great weekend. By the way, thanks for your helps on the message "Need Help on Define Names" that I posted on 09/19/09. I apologize for not able to respond to your message. "Dave Peterson" wrote: Somethings cause xl to recalculate and think that that linked cell (or list) changed (I'm not sure which one). I'm not sure what they are or when they do it (what version of excel are you using?). Just a couple of days ago, there was another user who had the same problem. I couldn't duplicate it xl2003 (which is what they were using, too(!)). All I know is that sometimes it's just easier to use code than to struggle. Souny wrote: Dave, Thank you very much for the code. I will try it and let you know. One thing that does not make sense to me. Why do we have to go through the code to populate the values, and why my setup would not work? I thought the intend of the combo box is to execute the code when we click on the combo box and when the value changes in the combo box, not to execute automatically when the file opens. Am I correct? Did I miss something during the setup that you can think of? Thanks again. "Dave Peterson" wrote: Say you have the list in a range on a separate sheet named "ListInfo" in A1:A10. And you want the linked cell on the same worksheet as the combobox in A1. And you want to populate the combobox when the workbook opens. My combobox was named combobox1 and is on Sheet1. I had this code in a General module: Option Explicit Sub Auto_Open() Dim wks As Worksheet Dim ListRng As Range Set wks = Worksheets("Sheet1") With Worksheets("ListInfo") Set ListRng = .Range("A1:A5") End With wks.OLEObjects("combobox1").Object.List = ListRng.Value End Sub Behind the worksheet with the combobox: Option Explicit Private Sub ComboBox1_Change() Me.Range("A1").Value = Me.ComboBox1.Value End Sub ======== I have no idea how often that combobox needs to have its list refreshed. Maybe using a different event would make more sense. Souny wrote: Dave, Thanks for your response. Could you help me with the code? "Dave Peterson" wrote: You may want to just bite the bullet and drop the linkedcell and rowsource and replace them with code. Souny wrote: Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Hope it works for you.
Souny wrote: Dave, Thanks for the explanation. I am using Excel 2003 Standard and Windows XP Profession. Have a great weekend. By the way, thanks for your helps on the message "Need Help on Define Names" that I posted on 09/19/09. I apologize for not able to respond to your message. "Dave Peterson" wrote: Somethings cause xl to recalculate and think that that linked cell (or list) changed (I'm not sure which one). I'm not sure what they are or when they do it (what version of excel are you using?). Just a couple of days ago, there was another user who had the same problem. I couldn't duplicate it xl2003 (which is what they were using, too(!)). All I know is that sometimes it's just easier to use code than to struggle. Souny wrote: Dave, Thank you very much for the code. I will try it and let you know. One thing that does not make sense to me. Why do we have to go through the code to populate the values, and why my setup would not work? I thought the intend of the combo box is to execute the code when we click on the combo box and when the value changes in the combo box, not to execute automatically when the file opens. Am I correct? Did I miss something during the setup that you can think of? Thanks again. "Dave Peterson" wrote: Say you have the list in a range on a separate sheet named "ListInfo" in A1:A10. And you want the linked cell on the same worksheet as the combobox in A1. And you want to populate the combobox when the workbook opens. My combobox was named combobox1 and is on Sheet1. I had this code in a General module: Option Explicit Sub Auto_Open() Dim wks As Worksheet Dim ListRng As Range Set wks = Worksheets("Sheet1") With Worksheets("ListInfo") Set ListRng = .Range("A1:A5") End With wks.OLEObjects("combobox1").Object.List = ListRng.Value End Sub Behind the worksheet with the combobox: Option Explicit Private Sub ComboBox1_Change() Me.Range("A1").Value = Me.ComboBox1.Value End Sub ======== I have no idea how often that combobox needs to have its list refreshed. Maybe using a different event would make more sense. Souny wrote: Dave, Thanks for your response. Could you help me with the code? "Dave Peterson" wrote: You may want to just bite the bullet and drop the linkedcell and rowsource and replace them with code. Souny wrote: Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Dave,
I have a quick question. What is the purpose of using the "Option Explicit"? Thanks. "Dave Peterson" wrote: Hope it works for you. Souny wrote: Dave, Thanks for the explanation. I am using Excel 2003 Standard and Windows XP Profession. Have a great weekend. By the way, thanks for your helps on the message "Need Help on Define Names" that I posted on 09/19/09. I apologize for not able to respond to your message. "Dave Peterson" wrote: Somethings cause xl to recalculate and think that that linked cell (or list) changed (I'm not sure which one). I'm not sure what they are or when they do it (what version of excel are you using?). Just a couple of days ago, there was another user who had the same problem. I couldn't duplicate it xl2003 (which is what they were using, too(!)). All I know is that sometimes it's just easier to use code than to struggle. Souny wrote: Dave, Thank you very much for the code. I will try it and let you know. One thing that does not make sense to me. Why do we have to go through the code to populate the values, and why my setup would not work? I thought the intend of the combo box is to execute the code when we click on the combo box and when the value changes in the combo box, not to execute automatically when the file opens. Am I correct? Did I miss something during the setup that you can think of? Thanks again. "Dave Peterson" wrote: Say you have the list in a range on a separate sheet named "ListInfo" in A1:A10. And you want the linked cell on the same worksheet as the combobox in A1. And you want to populate the combobox when the workbook opens. My combobox was named combobox1 and is on Sheet1. I had this code in a General module: Option Explicit Sub Auto_Open() Dim wks As Worksheet Dim ListRng As Range Set wks = Worksheets("Sheet1") With Worksheets("ListInfo") Set ListRng = .Range("A1:A5") End With wks.OLEObjects("combobox1").Object.List = ListRng.Value End Sub Behind the worksheet with the combobox: Option Explicit Private Sub ComboBox1_Change() Me.Range("A1").Value = Me.ComboBox1.Value End Sub ======== I have no idea how often that combobox needs to have its list refreshed. Maybe using a different event would make more sense. Souny wrote: Dave, Thanks for your response. Could you help me with the code? "Dave Peterson" wrote: You may want to just bite the bullet and drop the linkedcell and rowsource and replace them with code. Souny wrote: Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
It tells excel's VBA that you want to be forced to declare all the variables you
use. That way, if you do something like: Dim ctr1 as long ctr1 = ctrl + 1 You won't have to spend hours finding the typo (1 vs l (one vs ell)). You'd get a compile error right away. Souny wrote: Dave, I have a quick question. What is the purpose of using the "Option Explicit"? Thanks. "Dave Peterson" wrote: Hope it works for you. Souny wrote: Dave, Thanks for the explanation. I am using Excel 2003 Standard and Windows XP Profession. Have a great weekend. By the way, thanks for your helps on the message "Need Help on Define Names" that I posted on 09/19/09. I apologize for not able to respond to your message. "Dave Peterson" wrote: Somethings cause xl to recalculate and think that that linked cell (or list) changed (I'm not sure which one). I'm not sure what they are or when they do it (what version of excel are you using?). Just a couple of days ago, there was another user who had the same problem. I couldn't duplicate it xl2003 (which is what they were using, too(!)). All I know is that sometimes it's just easier to use code than to struggle. Souny wrote: Dave, Thank you very much for the code. I will try it and let you know. One thing that does not make sense to me. Why do we have to go through the code to populate the values, and why my setup would not work? I thought the intend of the combo box is to execute the code when we click on the combo box and when the value changes in the combo box, not to execute automatically when the file opens. Am I correct? Did I miss something during the setup that you can think of? Thanks again. "Dave Peterson" wrote: Say you have the list in a range on a separate sheet named "ListInfo" in A1:A10. And you want the linked cell on the same worksheet as the combobox in A1. And you want to populate the combobox when the workbook opens. My combobox was named combobox1 and is on Sheet1. I had this code in a General module: Option Explicit Sub Auto_Open() Dim wks As Worksheet Dim ListRng As Range Set wks = Worksheets("Sheet1") With Worksheets("ListInfo") Set ListRng = .Range("A1:A5") End With wks.OLEObjects("combobox1").Object.List = ListRng.Value End Sub Behind the worksheet with the combobox: Option Explicit Private Sub ComboBox1_Change() Me.Range("A1").Value = Me.ComboBox1.Value End Sub ======== I have no idea how often that combobox needs to have its list refreshed. Maybe using a different event would make more sense. Souny wrote: Dave, Thanks for your response. Could you help me with the code? "Dave Peterson" wrote: You may want to just bite the bullet and drop the linkedcell and rowsource and replace them with code. Souny wrote: Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
Good morning Dave,
Thank you very much for the detail explanation. If I may, I would like to ask you a question on querytables. I have been looking for the answer to that question, and I have not had any luck. From the projects that I work on, I connect an Excel file to an Access database to retrieve data. For the connection, I do not use code, instead I connect them through Data Import External Data New Database Query. Part of the connection setup, I give a querytable name and check the box "Save query definition" in the External Data Range Properties. If I want to delete the querytable that I created, I uncheck the Save Query Definition box on the properties and delete the define name of that querytable. After I uncheck and delete the define name of that querytable, when I recreate a new querytable using the same querytable name that I just deleted, I would not able to use it. I receive an error message that the name is already exist on that sheet. I thought I deleted, and why would the querytable name still exist? Is there a way to delete the querytables permanently so that when I want to use the same name again, I would be able to use it? Thanks. "Dave Peterson" wrote: It tells excel's VBA that you want to be forced to declare all the variables you use. That way, if you do something like: Dim ctr1 as long ctr1 = ctrl + 1 You won't have to spend hours finding the typo (1 vs l (one vs ell)). You'd get a compile error right away. Souny wrote: Dave, I have a quick question. What is the purpose of using the "Option Explicit"? Thanks. "Dave Peterson" wrote: Hope it works for you. Souny wrote: Dave, Thanks for the explanation. I am using Excel 2003 Standard and Windows XP Profession. Have a great weekend. By the way, thanks for your helps on the message "Need Help on Define Names" that I posted on 09/19/09. I apologize for not able to respond to your message. "Dave Peterson" wrote: Somethings cause xl to recalculate and think that that linked cell (or list) changed (I'm not sure which one). I'm not sure what they are or when they do it (what version of excel are you using?). Just a couple of days ago, there was another user who had the same problem. I couldn't duplicate it xl2003 (which is what they were using, too(!)). All I know is that sometimes it's just easier to use code than to struggle. Souny wrote: Dave, Thank you very much for the code. I will try it and let you know. One thing that does not make sense to me. Why do we have to go through the code to populate the values, and why my setup would not work? I thought the intend of the combo box is to execute the code when we click on the combo box and when the value changes in the combo box, not to execute automatically when the file opens. Am I correct? Did I miss something during the setup that you can think of? Thanks again. "Dave Peterson" wrote: Say you have the list in a range on a separate sheet named "ListInfo" in A1:A10. And you want the linked cell on the same worksheet as the combobox in A1. And you want to populate the combobox when the workbook opens. My combobox was named combobox1 and is on Sheet1. I had this code in a General module: Option Explicit Sub Auto_Open() Dim wks As Worksheet Dim ListRng As Range Set wks = Worksheets("Sheet1") With Worksheets("ListInfo") Set ListRng = .Range("A1:A5") End With wks.OLEObjects("combobox1").Object.List = ListRng.Value End Sub Behind the worksheet with the combobox: Option Explicit Private Sub ComboBox1_Change() Me.Range("A1").Value = Me.ComboBox1.Value End Sub ======== I have no idea how often that combobox needs to have its list refreshed. Maybe using a different event would make more sense. Souny wrote: Dave, Thanks for your response. Could you help me with the code? "Dave Peterson" wrote: You may want to just bite the bullet and drop the linkedcell and rowsource and replace them with code. Souny wrote: Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with a combo box in a worksheet
I don't work with querytables enough to know.
If you don't get an answer in this thread, you may want to start a new one. You may get more eyeballs. Souny wrote: Good morning Dave, Thank you very much for the detail explanation. If I may, I would like to ask you a question on querytables. I have been looking for the answer to that question, and I have not had any luck. From the projects that I work on, I connect an Excel file to an Access database to retrieve data. For the connection, I do not use code, instead I connect them through Data Import External Data New Database Query. Part of the connection setup, I give a querytable name and check the box "Save query definition" in the External Data Range Properties. If I want to delete the querytable that I created, I uncheck the Save Query Definition box on the properties and delete the define name of that querytable. After I uncheck and delete the define name of that querytable, when I recreate a new querytable using the same querytable name that I just deleted, I would not able to use it. I receive an error message that the name is already exist on that sheet. I thought I deleted, and why would the querytable name still exist? Is there a way to delete the querytables permanently so that when I want to use the same name again, I would be able to use it? Thanks. "Dave Peterson" wrote: It tells excel's VBA that you want to be forced to declare all the variables you use. That way, if you do something like: Dim ctr1 as long ctr1 = ctrl + 1 You won't have to spend hours finding the typo (1 vs l (one vs ell)). You'd get a compile error right away. Souny wrote: Dave, I have a quick question. What is the purpose of using the "Option Explicit"? Thanks. "Dave Peterson" wrote: Hope it works for you. Souny wrote: Dave, Thanks for the explanation. I am using Excel 2003 Standard and Windows XP Profession. Have a great weekend. By the way, thanks for your helps on the message "Need Help on Define Names" that I posted on 09/19/09. I apologize for not able to respond to your message. "Dave Peterson" wrote: Somethings cause xl to recalculate and think that that linked cell (or list) changed (I'm not sure which one). I'm not sure what they are or when they do it (what version of excel are you using?). Just a couple of days ago, there was another user who had the same problem. I couldn't duplicate it xl2003 (which is what they were using, too(!)). All I know is that sometimes it's just easier to use code than to struggle. Souny wrote: Dave, Thank you very much for the code. I will try it and let you know. One thing that does not make sense to me. Why do we have to go through the code to populate the values, and why my setup would not work? I thought the intend of the combo box is to execute the code when we click on the combo box and when the value changes in the combo box, not to execute automatically when the file opens. Am I correct? Did I miss something during the setup that you can think of? Thanks again. "Dave Peterson" wrote: Say you have the list in a range on a separate sheet named "ListInfo" in A1:A10. And you want the linked cell on the same worksheet as the combobox in A1. And you want to populate the combobox when the workbook opens. My combobox was named combobox1 and is on Sheet1. I had this code in a General module: Option Explicit Sub Auto_Open() Dim wks As Worksheet Dim ListRng As Range Set wks = Worksheets("Sheet1") With Worksheets("ListInfo") Set ListRng = .Range("A1:A5") End With wks.OLEObjects("combobox1").Object.List = ListRng.Value End Sub Behind the worksheet with the combobox: Option Explicit Private Sub ComboBox1_Change() Me.Range("A1").Value = Me.ComboBox1.Value End Sub ======== I have no idea how often that combobox needs to have its list refreshed. Maybe using a different event would make more sense. Souny wrote: Dave, Thanks for your response. Could you help me with the code? "Dave Peterson" wrote: You may want to just bite the bullet and drop the linkedcell and rowsource and replace them with code. Souny wrote: Tom, Could you help me one thing? I have a combo box in one of my worksheets, and the code for the combo box is also in the same worksheet "Sheet1". Basically, the code will execute the lines based on the value selected (from clicking) from the combo box. Somehow, the code automatically executes when the file is opened. I have tried many ways, and I am still having problems. I don't understand why the code will automatically execute when the file is opened. Below is my code structure. Private Sub cboCode_Click() Application.ScreenUpdating=False Select Case cboCode.Value Case "Selection1" 'Do the following Case "Selection2" 'Do the following Cash "Selection3" 'Do the following Case Else 'Do the following End Select Application.ScreenUpdating = True End Sub Please help. Thanks. "Tom Ogilvy" wrote: I depends on what you think the normal rules are. right click on the combobox and select properties. in rowsource, the link for the dropdown list, put something like Sheet1!A1:A30 in Control source (the link for the result) sheet2!B9 -- Regards, Tom Ogilvy "Andy the yeti" wrote: Hi, Due to some superb help from these groups I have almost achieved what I wanted to do using a userform, I have one last hurdle now that involves using a combo box on a userform. Do these follow the rules of normal of combo boxes in regards to a cell link and input range? If so could I have some pointers please on how to set up the properties? Thank you very much Andy -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform Initialize & combo box values | Excel Discussion (Misc queries) | |||
Userform combo box triggering macros | Excel Discussion (Misc queries) | |||
UserForm and combo box to another sheet | Excel Discussion (Misc queries) | |||
How to get a range of values into a combo placed on USERFORM? | Excel Programming | |||
Combo Box in userform | Excel Programming |