Rows.AutoFit triggering cboComboBox_Change subroutine 1

[MattGreer]

I have a table that, when sorted, needs the rows to be autofit so they adjust to the user can see what's in all the rows and such. Not every row has extra info, so when the rows get sorted/filtered, etc. they don't keep their individual row heights. By the way, if there's a way around that, please let me know.

So at the end of one of these subroutines I wrote, I put:

Application.EnableEvents = False
Rows.AutoFit
ReSort
Application.EnableEvents = True

Another problem is that when you autofit all the rows, it visually removes the filtering, so the 'ReSort' sub just reapplies whatever sort was last used.

I thought the Application.EnableEvents would prevent the combo box from retriggering itself, but that's not working. Even with that code in there, the combo box thinks something has changed, so the combo box code runs again and triggers the whole set of routines. The problem is that, when the whole routine gets triggered again, for whatever reason I get an error. Forget the error for now. Is there a way to keep the combo box from thinking it got changed?

Thanks!

Thanks!!


Matt

 

[SkipVought]

Hi,

You apparently have a lot of things going on in your sheet. You may need to remove some portions, try this autofit and then add an element back in until you find a breaking point. But try this method first.

Application.EnableEvents = False
Activesheet.UsedRange.EntireRow.AutoFit
ReSort
Application.EnableEvents = True

I've got no idea exactly what your ReSort procedure does. That could contribute to your issues???


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[MattGreer]

ReSort is just:

Public Sub ReSort()
    ActiveSheet.ListObjects("tbl").AutoFilter.ApplyFilter
    With ActiveWorkbook.Worksheets("List").ListObjects("tbl").Sort
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Your suggested modification seemed to work at first, and then it didn't. Not sure why.

I guess I could set a boolean variable (idk what they're called, global variables?) that could prevent the cbo code from running, and then set it once we get out of the sub.

Thanks!!


Matt

 

[MattGreer]

OK, so this is interesting, might be a clue as to what's going on.

When I set the filter using the combo box, if I select another filter using the cbo before resetting/clearing it (there's a linked cell), I get the error. If I clear it, I don't get the error the next time I use the cbo box.

Hmmm...

Thanks!!


Matt

 

[SkipVought]

Q1: Is there any reason why you're NOT using Structured Tables, which has Filters automatically?

Q2: Why are you using a Combobox to set filters rather than the filters themselves?

Q3: Like I stated, you've got a lot going on, and now there's the combobox macro that we haven't seenyet?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

...and on your ReSort...ah, you are using a Structured Table...

Public Sub ReSort()
    With ActiveSheet.ListObjects("tbl")
        .AutoFilter.ApplyFilter
        With .Sort
            .header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

So why do you turn OFF your Filters?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[MattGreer]

I always use Structured Tables. They make things sooooo much easier I think.

I mean, I don't turn *off* the filters (I don't think I do?) One of the things I use 'ReSort' is if I take the table, add some additional filters, or change something, then 'ReSort' just reapplies the previous sort. Just a bit more convenient to click a button. I have buttons driving most or all of the macros.

I'm using the combo box to set filters because of what I'm filtering by. So this spreadsheet is related to this game my daughter and I play, called The Battle Cats (lol). It's actually a pretty deep game and from a calculation/statistical perspective it's piqued my interest. I'll try to keep this short.

I have a list of in-game cats I own. They have different abilities, and sometimes they have strengths against certain enemies. Those enemies have categories like "Black, Red, Alien, Zombie" and so forth. There's one column called 'Enemy Types' that contains a list of enemies against which this cat has special abilities. I use the combo box to select an enemy type to filter by, rather than having to go to the enemies column, select the filter, clear the filter, go down to 'Text Filters', then select 'Contains', then enter the text, etc. etc. etc. The purpose of the combo box is to speed up filtering the table by "enemy type".

This *really* belongs in a database but I haven't quite nailed down the database schema.

I'd be happy to send you the workbook if you want to look at it; I'd prefer not to upload it here tho.

Thanks!!


Matt

 

[MattGreer]

Yeah, I've confirmed it. The error goes away if I select a cell in the table first, then change the combo box.

Thanks!!


Matt