combobox

[ranswert]

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

 

[FSt1]

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....
insert>name>define. 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]

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.