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]

...and your CODE, or should we play 20 questions?

Skip,

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

 

[jsbc]

The code as i said works fine without the active combo box

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

Row 12 is the one that contains the combo box and the execution stops there

Hope that helps

Jochen

 

[SkipVought]

What kind of control: Control Toolbox, Form or Data Validation?

Skip,

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

 

[jsbc]

to replicate the problem:

step one - insert combo box with two or more options

step two - select combo box and change value (leave selected)

step three - hide row

step four - watch your excel crash

This looks like an Excel problem to me. Without code all you have to do is selecting a different cell and everything is fine. However, in code with the select function it does select the cell but still crashes

Jochen

 

[SkipVought]

WHAT KIND OF CONTROL???

Please answer the question.

Skip,

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

 

[jsbc]

Sorry, I thought the word Combo Box was what you were looking for, its a Control Toolbox

Jochen

 

[SkipVought]

Sure did! Going to have to think about this one.

Skip,

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

 

[SkipVought]

Rather than hiding ROWS (there are no lines in Excel, other than drawing objects), assign the visible property of the control to false and true to hide and make visible.

Skip,

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

 

[jsbc]

Problem with that is the amount of text and so on that will distract the user in those rows will need to be hidden as well. Hiding the rows results in a nice compact input sheet that adjusts itself to what is required and in the order it is required. (and it is not a single case in this workbook, but rather the norm)

As I said, works all fine until you forget to click a cell after using the combo box.

Jochen

 

[SkipVought]

Change the Properties Tab of Format > Control FROM move & size with cell to one of the two other options AND then change the Visible property along with hiding the rows.

Skip,

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

 

[jsbc]

That solved the problem of crashing, thanks. However, I now have a visible combo box that stays until you refresh excel. That should be solved more easily though

Thanks again

Jochen

 

[jsbc]

Hi Skip,

got a new problem now, the combo boxes further down are not staying in their row anymore. The rows are changing in a random pattern, so there are only fixed locations for the topmost box

any ideas what I can do?

Jochen

 

[SkipVought]

Reposition using the Top and Left properties of the controls and dhte Top and Left properties of the cells as needed.

Skip,

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

 

[jsbc]

Problem is, the top properties are variables according to which rows are hidden.

The only way it actually works is by leaving the properties with Move and size and setting the placement to 2

However, that is the case where excel crashes

Might have to find another way of dealing with this

Jochen

 

[SkipVought]

"Problem is, the top properties are variables according to which rows are hidden."

Does not make sense. Let's say that F5 is the cell below the top left corner of the control. I would think that you would always want the contol to be positioned at F5. Yes?

Skip,

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

 

[combo]

You can combine hiding control with hiding rows:

Private Sub cmdHide_Click()
Me.ComboBox1.Visible = False
Me.Rows(12).Hidden = True
End Sub

Private Sub cmdShow_Click()
Me.ComboBox1.Visible = True
Me.Rows(12).Hidden = False
End Sub

combo

 

[jsbc]

Re Skip:

Yes that is correct. What I see at the moment is my control sliding down the page when the rows get hidden and staying in this position relative to the cell the now occupy once unhidden. (Using anything but the move and size properties)

Re Combo:
This is essentially what I have done, just combined the row hiding into one comand.

Jochen

 

[SkipVought]

So has that solved your problem?

Skip,

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

 

[jsbc]

Now I am confused (BTW English is not my first language)

The problem still stands: Either the boxes stay in their appropriate cells and excel crashes, or the cells slide down and excel works fine (in this case, the row with the active box is not hidden, as the box is not anchored there).

Now I need to work on the: staying in the right cell and excel does not crash variation As far as I can see it, the Top value changes when rows between the top of the page and the cell (in your example F5) are hidden. That results in a variable Top property depending on how many rows are hidden at any given moment. Or am I wrong about that.

I still think this is a simple user problem.

Do you have any idea why the following code

Sheet().Range("F5").Select

does not execute the same as if you would manually click on F5? The manual part solves my problem (however, I would like to cater a user not remembering this in a crucial moment) and I would like to include it in code

Cheers
Jochen