Hiding a line with activ combo box

[jsbc]

Hi there,

I have the following problem. I have a macro that hides certain lines to allow easier input when those lines are not required. Part of these lines are combo boxes. It all works fine until a special case arises: The combo box has been changed and the button to initiate the hide function is click straight away while the combo box is still selected. In this case excel crashes and needs to be restarted. The problem can be simulated manually using the hide function in excel directly.

The solution is easy as well, just click in a cell before hitting the button works and no conflicts arise.

I tried to use:

Sheets().Range().Select

in the macro, however, it selects the cell, but still does not stop excel from crashing. (When I do it manually, no problem, but in code it does not work). The steps get not recorded when using the record macro function :-(

Question: What would be the appropriate code sequence to solve this?

Cheers
Jochen

 

[SkipVought]

Why have you ignored the suggestions (mine AND combo's) regarding making the control visible property FALSE and repositioning the control when you make it visible?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[jsbc]

I think we are talking two different things here,

I have done exactly what combo has suggested, the full code has not been displayed as it just caters for load cases and when rows need to be hidden or not. The above code is (one else function) for hiding most rows and setting up a button to add the rows again. And it is just an example from one of the worksheets to illustrate the problem. (I took the smallest and easiest one). I left out the unhiding option as it is the same code with different row allocations and different true/false settings

Could you please elaborate on how I should reposition when making visible as I do not understand what you are telling me. There is no fixed Top position, so I cannot enter a value through code. Sometimes there are 5 rows hidden, sometimes two, etc (between the box and the top of the page.

No I am confused, sorry

Jochen

 

[jsbc]

Here is the whole code for one input sheet

Public Sub EXT_Activate_Cell()
    Sheets("External Footing").Range("IPEXTWidth").Select
End Sub


Public Sub CBEXTPilingGeoAdd_click()
    Sheets("Data").Range("EXTPilingGeoStandard") = False
    EXT_Geotech_Adjust_Case1
End Sub

Public Sub CBEXTPilingGeoRemove_click()
    Sheets("Data").Range("EXTPilingGeoStandard") = True
    EXT_Geotech_Adjust_Case1
End Sub

Public Sub CBEXTPilingAdd_click()
    EXT_Activate_Cell
    Sheets("Data").Range("EXTPilingReq") = True
    EXT_Piling_Adjust
End Sub

Public Sub CBEXTPilingRemove_click()
    EXT_Activate_Cell
    Sheets("Data").Range("EXTPilingReq") = False
    EXT_Piling_Adjust
End Sub





Public Sub EXT_Piling_Adjust()
    EXT_Piling_Adjust_Case1
End Sub

Public Sub EXT_Storey_Adjust()
    'EXT_Storey_Adjust_Case2
End Sub

Public Sub EXT_Geotech_Adjust_Case1()
    If Sheets("Data").Range("EXTPilingGeoStandard") = True Then
        Sheets("External Footing").Range("IPEXTPilingEnd") = "=EXTPilingEndBearingCalc"
        Sheets("External Footing").Range("IPEXTPilingSkin") = "=EXTPilingSkinFrictionCalc"
        Sheets("External Footing").Rows("13:14").Hidden = True
        CBEXTPilingGeoAdd.Visible = True
        CBEXTPilingGeoRemove.Visible = False
    Else
        Sheets("External Footing").Rows("13:14").Hidden = False
        CBEXTPilingGeoAdd.Visible = False
        CBEXTPilingGeoRemove.Visible = True
    End If
End Sub

Public Sub EXT_Piling_Adjust_Case1()
    If Sheets("Data").Range("EXTPilingReq") = True Then
        CBEXTPilingAdd.Visible = False
        Sheets("External Footing").Rows("10").Hidden = True
        Sheets("External Footing").Rows("12").Hidden = False
        EXT_Geotech_Adjust_Case1
        Sheets("External Footing").Rows("15:19").Hidden = False
        CBEXTPilingRemove.Visible = True
        EXT_Piling_Mass_Adjust_Case1
    Else
        CBEXTPilingAdd.Visible = True
        CBEXTPilingRemove.Visible = False
        CBEXTPilingGeoAdd.Visible = False
        CBEXTPilingGeoRemove.Visible = False
        Shapes("CBEXTPileMassDia").Visible = False
        Shapes("CBEXTPileDia").Visible = False
        Shapes("CBEXTStirrups").Visible = False
        Sheets("External Footing").Rows("10").Hidden = False
        Sheets("External Footing").Rows("12:19").Hidden = True
    End If
End Sub

Public Sub EXT_Piling_Mass_Adjust_Case1()
    If Sheets("Data").Range("EXTPilingMassReq") = True Then
        Sheets("Data").Range("EXTPilingMassCheck") = True
        Sheets("External Footing").Range("IPEXTStirrups") = "No"
        Shapes("CBEXTPileMassDia").Visible = True
        Shapes("CBEXTPileDia").Visible = False
        Shapes("CBEXTStirrups").Visible = False
        Sheets("External Footing").Rows("16").Hidden = True
    Else
        Sheets("Data").Range("EXTPilingMassCheck") = False
        Shapes("CBEXTPileMassDia").Visible = False
        Shapes("CBEXTPileDia").Visible = True
        Shapes("CBEXTStirrups").Visible = True
        Sheets("External Footing").Rows("16").Hidden = False
    End If
End Sub

Hope that helps

Jochen

 

[jsbc]

As I said, the code works fine up to the point where any Combo Box is active and the row that it is in gets hidden.

I included the Select option the activate a cell and then hide the rows, but that does not work.

Hope this makes sense

Jochen

 

[combo]

Jochen,
I was forced excel to crash following the steps you posted (without code). This happens only when the control is selected.
So, for the activex combo box (with move & size with cell set) I used the code I posted above. The code (hide & unhide) in the worksheet's module is triggered by command button (again, activex) on the worksheet. It works, i.e. in my case excel does not crash.
I tried to refer to the control as shape. Excel started to crash. After change Shapes => OLEObjects it was working again.

combo

 

[jsbc]

Thanks, it was the Shape, after changing it no further problems. Greatly appreciated

Jochen