selecting a sheet - Xtreme Visual Basic Talk

09-21-2003, 09:01 PM

Dangleberry Dangleberry is offline

Contributor

Join Date: Sep 2003

Location: Australia

Posts: 413

selecting a sheet

Hi guys,

Having a bit of trouble getting some code to work. I'm new to this caper, so any tips on neatening up my code
and the like would be greatly appreciated.

I'm using Visual Basic for excel in office 2000 and what I'm trying to do is to activate a sheet in an open workbook.
The sheetname is entered via an input box and if the sheet name is entered incorrectly I want an error message to
come up letting the user know and give them the option to re-enter the sheet name. Here is the code I have for it:

Code:

8 freightsheet = InputBox("Please Enter the name of the sheet that the freight information_
 is located on in the file " & freightfile, "Sheet Name", "Sheetname")
If freightsheet = "" Then End
On Error GoTo 16
Sheets(freightsheet).Activate
On Error GoTo 0

16 response = MsgBox("The sheet name you selected could not be found in the workbook "_
 & freightname & ". Click 'Yes' to re-enter the sheet name or click 'No' to exit.",_
 vbYesNo, "Incorrect Sheet Name, File Name Combination")
If response = vbYes Then GoTo 8 Else End

It works for the first loop, but if it is entered incorrectly second time around an error message comes up:

"Run time error '9':

Subscript out of range."

09-21-2003, 10:30 PM

crabby

mostly gone

Retired Moderator
* Expert *

Join Date: Aug 2002

Location: London / UK

Posts: 2,721

i cant see that you clear freightsheet after the first loop. so it will still hold the old value, not certain but a

Code:

freightsheet = ""

before requereing could be usefull

__________________
there are always two sides of a story and 12 ways to sing a song or write some code. so whats wrong ?

Avatar by lebb

09-21-2003, 10:48 PM

Dangleberry Dangleberry is offline

Contributor

Join Date: Sep 2003

Location: Australia

Posts: 413

Hmm... tried it, still didn't work

If on the second loop I put in a valid sheet name the code works. When the error came up i checked the
value of freightsheet, and it was the value I entered in the input box. I'm stumped anyway. I'm sure there's
a simple solution, I just can't see it! Thanks for the help though!

09-21-2003, 10:55 PM

mathiasb

Centurion

Join Date: Jul 2003

Location: San Francisco, CA

Posts: 183

Instead of using your code, I rewrote it quite a bit - mostly because I avoid using GoTo... Hope that helps,

Mathias

Code:

Sub activateSheet()

Dim mySheet As Worksheet
Dim sheetFound As Boolean

Dim mySheetName As String

mySheetName = InputBox("Enter the name of the sheet")

If mySheetName = "" Then Exit Sub

sheetFound = False
For Each Worksheet In Worksheets
    If Worksheet.Name = mySheetName Then
        Set mySheet = Worksheet
        sheetFound = True
    End If
Next

If sheetFound = False Then
    activateSheet
Else
    mySheet.Activate
End If

End Sub

09-21-2003, 10:57 PM

mathiasb

Centurion

Join Date: Jul 2003

Location: San Francisco, CA

Posts: 183

NB It's case sensitive, by the way

09-21-2003, 11:21 PM

Dangleberry Dangleberry is offline

Contributor

Join Date: Sep 2003

Location: Australia

Posts: 413

Its like you're talking half in english and half in Dutch!!!
I think if i have a bit of a play it should
start to make sense. Picked up a few handy tips, thanks. I'm guessing 'Exit Sub' stops the particular
sub, but not the subs that are running it. (hmmm.... think I'm talking in circles!)

If I want to hold onto the value of mysheet in other subs how do I do that?

Thanks Again

That works perfectly!!! It all makes sense too! I've learnt a fair bit from that, Ta.

Alex

Last edited by Dangleberry; 09-21-2003 at 11:53 PM.

09-22-2003, 12:00 AM

mathiasb

Centurion

Join Date: Jul 2003

Location: San Francisco, CA

Posts: 183

If your questions are still questions...

1. What you could do to hold the value is rewrite this into a function that just returns true if it is a valid sheet name, false else, without selecting/activating it, or sth along these lines.

2. Correct, exit sub, as the name suggest, exits the current sub; so it returns in the sub that called it.

Cheers,

Mathias

PS And I am largely French and partly German

which does not exactly explain how I talk half English and half Dutch!

09-22-2003, 12:30 AM

Dangleberry Dangleberry is offline

Contributor

Join Date: Sep 2003

Location: Australia

Posts: 413

All is good! you've helped me get it working like a well oiled machine! Thanks a million!

Ahh.. French and German is it, not surprising really, I never was any good at languages!