combobox
I have the following code for my combobox in a form:
Private Sub ComboBox1_DropButtonClick() unprotectsheet stopautocalc Range("drwlistrng").EntireRow.Hidden = False Sub stopautocalc() With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With End Sub Sub startautocalc() With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With End Sub End Sub I've tried it with both 'stopautocalc' and 'startautocalc'. The problem I am having is the combobox isn't using the right range of cells all the time. It seems to happen when I add rows above the range that the combobox is supposed to pull up. What am I doing wrong? Thanks |
combobox
hi
you are adding rows above the range that the combobox is supposed to pull up without adjusting the combo box's row source.(list file range) excel will compensate and adjust formula on the sheet when rows/columns are added or deleted but object are a different matter. they don't adjust automaticly. easiest solution is declare your combo box row sorce(lit fill range) a named range. high light the data then on the menu bar.... insertnamedefine. type a name into the name box. click add, click ok then change your combo box's row sorce(list fill range) to the range name you just created. xl will adjust the addres of the named range as you add and delete rows/columns but the combo box will always reference the named range no mater where it is. regards FSt1 "ranswert" wrote: I have the following code for my combobox in a form: Private Sub ComboBox1_DropButtonClick() unprotectsheet stopautocalc Range("drwlistrng").EntireRow.Hidden = False Sub stopautocalc() With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With End Sub Sub startautocalc() With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With End Sub End Sub I've tried it with both 'stopautocalc' and 'startautocalc'. The problem I am having is the combobox isn't using the right range of cells all the time. It seems to happen when I add rows above the range that the combobox is supposed to pull up. What am I doing wrong? Thanks |
combobox
I did do that. I named the range 'drwlistrng' and had that listed for the
row source for the combo box. For some reason it is not being adjusted. I don't know if it has something to do with the event change that takes place on the worksheet. I use 'unprotectsheet' , 'protectsheet', along with 'stopautocalc' and 'startautocalc' running inside of different procedures on this sheet. "FSt1" wrote: hi you are adding rows above the range that the combobox is supposed to pull up without adjusting the combo box's row source.(list file range) excel will compensate and adjust formula on the sheet when rows/columns are added or deleted but object are a different matter. they don't adjust automaticly. easiest solution is declare your combo box row sorce(lit fill range) a named range. high light the data then on the menu bar.... insertnamedefine. type a name into the name box. click add, click ok then change your combo box's row sorce(list fill range) to the range name you just created. xl will adjust the addres of the named range as you add and delete rows/columns but the combo box will always reference the named range no mater where it is. regards FSt1 "ranswert" wrote: I have the following code for my combobox in a form: Private Sub ComboBox1_DropButtonClick() unprotectsheet stopautocalc Range("drwlistrng").EntireRow.Hidden = False Sub stopautocalc() With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With End Sub Sub startautocalc() With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With End Sub End Sub I've tried it with both 'stopautocalc' and 'startautocalc'. The problem I am having is the combobox isn't using the right range of cells all the time. It seems to happen when I add rows above the range that the combobox is supposed to pull up. What am I doing wrong? Thanks |
All times are GMT +1. The time now is 06:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com