• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Array for hidden sheets with part names

vasim

Member
Hello,


I have a combobox in which there are months form Jan to Dec and 1 as All,


There are a total of 12 sheets named as Jan 2010, Feb 2010...till Dec 2010, now when I select Jan in combobox and go the Jan 2010 sheet opens, the only problem is when I click All and go all the hidden sheets do not become visible. Help please


Private Sub CommandButton1_Click()

Dim x As String

Dim y As String

Dim Wsname As Variant


y = "2010"

x = combobox1.Value


If combobox1.Value = "All" Then

Application.Worksheets(array("" * " & y", "" * " & y")).Visible = True


Else

Application.Worksheets(x & " " & y).Visible = True

End If

Unload Me

End Sub


I know that the array is not working as the shets are hidden can i get some thing to solve....
 

Hui

Excel Ninja
Staff member
How about:

[pre]
Code:
Private Sub CommandButton1_Click()
Dim x As String
Dim y As String
Dim Wsname As Variant
Dim ws As Worksheet

y = "2010"
x = combobox1.Value

If combobox1.Value = "All" Then
For Each ws In Worksheets 'This loops through each WSheet and makes them visible
ws.Visible = True
Next

Else
Application.Worksheets(x & " " & y).Visible = True
End If
Unload Me
End Sub
[/pre]
 

vasim

Member
Thanks Hui, But I have many more sheets available, I want to make sheets visible only where there are months and 2010.
 

vasim

Member
This is working for me... Just a single question Hui, if you want to macth part text in vba we can use "*" before the actual text? is this correct.


Private Sub CommandButton1_Click()

Dim x As String

Dim y As String

Dim Wsname As Variant


y = "2010"

x = combobox1.Value

Application.ScreenUpdating = False

If combobox1.Value = "All" Then

For Each Wsname In Array("January 2010", "February 2010", "March 2010", "April 2010", "May 2010", "June 2010", "July 2010", "August 2010", "September 2010", "October 2010", "November 2010", "December 2010")

'For Each Wsname In Array("" * " & y", "" * " & y")

Worksheets(Wsname).Visible = True

Next


Else

Application.Worksheets(x & " " & y).Visible = True

End If

Application.ScreenUpdating = True

Unload Me

End Sub
 

Hui

Excel Ninja
Staff member
Private Sub CommandButton1_Click()

Dim x As String

Dim Yr As String

Dim Wsname As Variant

Dim ws As Worksheet


Yr = "2010"

x = combobox1.Value


If combobox1.Value = "All" Then

For Each ws In Worksheets 'This loops through each WSheet and makes them visible

If Right(ws.Name, 4) = "2010" Then ws.Visible = True

Next


Else

Application.Worksheets(x & " " & Yr).Visible = True

End If

Unload Me

End Sub
 

vasim

Member
Ohhhh just a few second late, else my code could be shorter than the one I have anyway..I got to learn something new...thanks a lot Hui
 

vasim

Member
Hello Hui,


For Each ws In Worksheets 'This loops through each WSheet and makes them visible

If Right(ws.Name, 4) = "2010" Then ws.Visible = True

Next


This makes the sheet unhide, however I have one more condition where I have to make these sheets hide


For Each ws In Worksheets 'This loops through each WSheet and makes them visible

If Right(ws.Name, 4) = "2010" Then ws.Visible = false

Next


Why is this not working
 

Luke M

Excel Ninja
The latter line of code works just fine. However, your 2 conditions are identical, so in the end all sheets ending with "2010" are hidden. Was this the desired effect?
 
Top