Form Control Combo Boxes Moving when rows hidden / unhidden

[umpirerob]

I have this crazy project in Excel 2010 and I have one issue plaguing it right now. I've used Combo Boxes to allow a value to be selected in one of two spots - two combo boxes each pointing to the same data in a cell. On a summary page I've created, I've used VBA to hide rows that don't contain relevant data and unhide them when they do. My issue is that the combo boxes mysteriously move on top of each other when the workbook is started fresh - all on Row 9 where the first of these belongs. Each "line" of data consists of two rows - all combo boxes are on odd numbered rows. All are set to "Move but don't size with cells" in the Properties of the Format Control. The are not locked as the worksheet is protected but these need to remain user functional.

This code does the initial hiding/unhiding

Private Sub Worksheet_Activate()    
    Dim RowNum As Integer
    Dim HRows As Range
   
    Worksheets("Coaching Summary").UnProtect "password"
    Application.ScreenUpdating = False
   
    Set HRows = Range("HideRange")
    With ActiveSheet
   
    For RowNum = 9 To 428
        If HRows.Cells(RowNum, 1) = "Hide" Then ' No Data
            Worksheets("Coaching Summary").Rows(RowNum).Hidden = True
        End If
        If HRows.Cells(RowNum, 1) = "Don't Hide!" Then ' Data exists
            Worksheets("Coaching Summary").Rows(RowNum).Hidden = False
        End If
        If IsOdd(RowNum) Then
            Call Priority_Box_Change(RowNum)
        End If
    Next RowNum
  
    Range("b1").Select
    Worksheets("Coaching Summary").Protect "password"
    Application.ScreenUpdating = True
    End With
    
End Sub

This code is to make the combo boxes visible or not based on if the row is hidden or not:

Sub Priority_Box_Change(RowNum As Integer)
With ActiveSheet
Dim ws As Worksheet
Dim BoxName As String


BoxName = "Priority Box" + Str(RowNum)
Set ws = Worksheets("Coaching Summary")
        If Worksheets("Coaching Summary").Rows(RowNum).Hidden = True Then
            With ws.DropDowns(BoxName)
            .Visible = False
            End With
        End If
        If Worksheets("Coaching Summary").Rows(RowNum).Hidden = False Then
            With ws.DropDowns(BoxName)
            .Visible = True
            End With
        End If


End With
End Sub

All of my combo boxes are named Priority Box X with X being the row number they belong on. I can cut/paste them back where I want them manually (assuming they are visible), but there are 210 of them and they just do it again later.

What am I missing?

 

[umpirerob]

No ideas, huh? I'm thinking I'm going to simply have to change how I handle this altogether by writing the data to display dynamically rather than hiding areas without data. This just seemed easier at the time when I first implemented it.

 

[Maleficent]

Obviously no use to the OP after all this time but for anyone else stumbling across this post with the same issue, like I did..... it seems that the issue is with the size of the comboboxes. The combo box must be the same size as the cell in which it sits in order to move/hide/unhide with it without changing position.

 

[umpirerob]

Obviously no use to the OP after all this time but for anyone else stumbling across this post with the same issue, like I did..... it seems that the issue is with the size of the comboboxes. The combo box must be the same size as the cell in which it sits in order to move/hide/unhide with it without changing position.

I don't remember what I ended up doing, but it was far more elegant than what I was trying to do here.