Click here to Skip to main content
12,444,287 members (51,643 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: MS-Excel
How to use this below code ?
i want to select case



Sub SelectedWoksheets()
'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Dim ws As Worksheet
 
    For Each ws In ActiveWindow.SelectedSheets
        With ws
        
            'With Code Here
        
        End With
    Next ws
End Sub

[Edit]Code block added[/Edit]
Posted 3-Nov-12 5:33am
tcchean308
Updated 3-Nov-12 5:50am
ProgramFOX185.7K
v3
Comments
Richard MacCutchan 3-Nov-12 12:18pm
   
What do you mean you want to select case? In order to do what exactly? Please edit your question and explain what you are trying to do.
ridoy 3-Nov-12 16:20pm
   
totally unclear,elaborate it..

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

If i understand you well, you want to use Select Case ... End Select statement.
Select case ws.Name
    Case "Sheet1"
        'do something
    Case "Sheet2"
        'do something
    Case "Sheet3"
        'do something
    Case Else
        'do something
End Select

But if would like to go through the collection of worksheets to "find" the correct one by its name, better use something like this:
Function GetWoksheet(wshName As String, wbk As Workbook) As Worksheet
Dim wsh As Worksheet
 
On Error Goto Err_GetWoksheet
 
Set wsh = wbk.Workseets(wshName)
 
Exit_GetWoksheet:
    Set GetWoksheet = wsh
    Exit Function
 
Err_GetWoksheet:
    MsgBoxErr.Description, vbExclamation, Err.Number
    Set wsh = Nothing
    Resume Exit_GetWoksheet
 
End Function

Usage:
Set wsh =  GetWoksheet("Sheet4", ActiveWorkbook)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160811.3 | Last Updated 4 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100