macro prompt to show/hide rows

[lindasf]

Hello,

I have created a macro to show selected rows or to show all rows.
would like to change it as follows:

Instead of typing in AP, CP, CW etc. it would be fewer keystrokes i
the user could just type in 1 through 10. (e.g I would have to equate
to AP, 2 to CP etc.)

On another note, if I kept the prompt for alpha (instead of numeric)
is it possible to NOT require the user to enter the value in uppe
case? In other words, could the user just type in ap (lower case) an
the macro would not match for case, and would return the rows for AP?

Thx. Much.

lindas

Attachment filename: all-demo-2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62861

 

[Jim Rech]

To get case insensitivity in the entire module put the Option Compare Text
at the top of the module.

This is a revised macro that lets numbers be entered:

Option Compare Text

Sub HideMostRows()
Dim sPrompt As String
Dim MatchCode As String
Dim C As Range
Application.ScreenUpdating = False
On Error GoTo ExitThis 'in case of a bad number
sPrompt = "Enter number:" & vbNewLine & _
"1. APS" & vbNewLine & _
"2. CAAP" & vbNewLine & _
"3. CalWORKs" & vbNewLine & _
"4. Family&Childrens" & vbNewLine & _
"5. Foster Care" & vbNewLine & _
"6. Food Stamps" & vbNewLine & _
"7. IHSS" & vbNewLine & _
"8. Investigations" & vbNewLine & _
"9. Medi-Cal" & vbNewLine & _
"10. Workforce Development"
x = InputBox(sPrompt)
MatchCode = Array("AP", "CP", "CW", "FA", "FC", "FS", "IH", "IN", "MC",
"WD")(x - 1)
Rows("2:11").Hidden = True
For Each C In Range("a2:a11")
If C.Value = MatchCode Then C.EntireRow.Hidden = False
Next
ExitThis:
End Sub


--
Jim Rech
Excel MVP
| Hello,
|
| I have created a macro to show selected rows or to show all rows. I
| would like to change it as follows:
|
| Instead of typing in AP, CP, CW etc. it would be fewer keystrokes if
| the user could just type in 1 through 10. (e.g I would have to equate 1
| to AP, 2 to CP etc.)
|
| On another note, if I kept the prompt for alpha (instead of numeric),
| is it possible to NOT require the user to enter the value in upper
| case? In other words, could the user just type in ap (lower case) and
| the macro would not match for case, and would return the rows for AP?
|
| Thx. Much.
|
| lindasf
|
| Attachment filename: all-demo-2.xls
| Download attachment:
http://www.excelforum.com/attachment.php?postid=628613
| ---
| Message posted
|