Solved: Using Select Case

[Opv]

I'm trying to determine how to use Select Case to selectively hide designated sets of non-contiguous columns. I am planning to create a user form that provides the user the option to click the appropriate button to create different views of a database, for example, "Auction View", "Kidding View", "Medical View" (and possibly more views to be added later). When the user selects the desired view, I am wanting all columns in the active worksheet (except a predefined set of columns) to be hidden.

I'm not all that familiar with Select Case. I've seen examples used to automatically change one or more cell values based on the value of another cell. I can't seem to grasp conceptually how it would be used in the above described application.

[Bob Phillips]

We need a ton more info.

What will be tested to determine what columns to show?

What columns are showing for what?

[Tinbendr]

I don't think you need Select Case in this instance.

From your description, the columns are either Hidden or not, thus offering only two choices. Select Case is used when multiple tests need to be made on a single parameter.

So, read this link (http://www.vbaexpress.com/forum/showthread.php?t=27649).They are using Togglebuttons, but they are either on or off, also.

An example of Select Case would be if you wanted to test a value in the a textbox.

Select Case Me.Textbox1

Case "ONE"
'more code here

Case "TWO"
'more code here

Case "THREE"
'more code here

End Select

While you COULD use a Select Case for two tests, it's kinda overkill.

[Opv]

We need a ton more info.

What will be tested to determine what columns to show?

What columns are showing for what?


As I said, I just trying to conceptualize how an already unfamiliar process might work. With that in mind, I'm thinking that each button on the user form would call a script, let's call it "createView(myView)". For example, the Auction View button would call it thusly, createView("Auction") and should effectively hide all data columns except A, C, E, F. The Kidding View button, on the other hand, would call "createView("Kidding") and should hide all data columns except A, B, G, H, I.

Once createView(myView) is called, it should respond based on which button was clicked.

Does that make any sense?

[Opv]

I don't think you need Select Case in this instance.

From your description, the columns are either Hidden or not, thus offering only two choices. Select Case is used when multiple tests need to be made on a single parameter.


Thanks. I'm still learning.

[mikerickson]

It sounds like you might be trying to duplicate the functionality of the built in Custom Views.

[Opv]

It appears you re right. Perhaps a more simple solution would be to explore how to automate the Custom View functionality. Thanks for bringing that to my attention.

Opv

[Opv]

It sounds like you might be trying to duplicate the functionality of the built in Custom Views.

Thanks again. That seems to do precisely what I want.

Opv

[Opv]

OK. What might be the problem with following code? It is currently hiding ALL columns rather than leaving columns 1-12 visible.


Dim cols As Integer: For cols = 1 To gCols

Select Case cols

Case 1 - 12
.Columns.Hidden = False
Case Else
.Columns.Hidden = True
End Select

Next

[Bob Phillips]

Dim cols As Integer: For cols = 1 To gCols

Select Case cols

Case 1 - 12
.Columns(cols).Hidden = False
Case Else
.Columns(cols).Hidden = True
End Select

Next


But you could simply use



Columns(1).Resize(, 12).Hidden = False
Columns(13).Resize(, gCols - 12).Hidden = True

[Opv]

Thanks. Makes sense now that I see it. Some times after I stare at something so long, I overlook the obvious.

Opv