How to open a specific worksheet via code from workbook_open 04-12-10

I have a combobox which is populated with all the worksheets within the
workbook. The controlsource of the combobox is linked to cell c5 on Sheet1

My question is how do i make the Excel workbook open at the worksheet which
is detailed within the combobox linked cell.

I've been previously using in the Workbook_Open procedure:-

Worksheets(Worksheets("sheet1").Range("c5").Value)

but the above code doesnt seem to work.

Hope you can help!

Cheers
0
Utf
4/12/2010 2:47:01 AM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
595 Views

Similar Articles

[PageSpeed] 1

Private Sub Workbook_Open()
'Sheet CodeName
'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Sheet1.Activate
End Sub



"Martin Parker" <MartinParker@discussions.microsoft.com> wrote in message 
news:DCA62FAD-48C6-4E9A-9768-53B9006A95B1@microsoft.com...
>I have a combobox which is populated with all the worksheets within the
> workbook. The controlsource of the combobox is linked to cell c5 on Sheet1
>
> My question is how do i make the Excel workbook open at the worksheet 
> which
> is detailed within the combobox linked cell.
>
> I've been previously using in the Workbook_Open procedure:-
>
> Worksheets(Worksheets("sheet1").Range("c5").Value)
>
> but the above code doesnt seem to work.
>
> Hope you can help!
>
> Cheers 

0
ozgrid
4/12/2010 2:56:22 AM
Worksheets(Worksheets("sheet1").Range("c5").Value).Activate


"Martin Parker" wrote:

> I have a combobox which is populated with all the worksheets within the
> workbook. The controlsource of the combobox is linked to cell c5 on Sheet1
> 
> My question is how do i make the Excel workbook open at the worksheet which
> is detailed within the combobox linked cell.
> 
> I've been previously using in the Workbook_Open procedure:-
> 
> Worksheets(Worksheets("sheet1").Range("c5").Value)
> 
> but the above code doesnt seem to work.
> 
> Hope you can help!
> 
> Cheers
0
Utf
4/12/2010 5:52:01 AM
Thanks that works great, however, one problem, if the user has previously 
closed the app on the worksheet the code is referring to, I get a runtime 
error on workbook open.

How would i code to check if the worksheet requested to open up at workbook 
open is already activated?

Hope this makes sense!

Cheers

"JLatham" wrote:

> Worksheets(Worksheets("sheet1").Range("c5").Value).Activate
> 
> 
> "Martin Parker" wrote:
> 
> > I have a combobox which is populated with all the worksheets within the
> > workbook. The controlsource of the combobox is linked to cell c5 on Sheet1
> > 
> > My question is how do i make the Excel workbook open at the worksheet which
> > is detailed within the combobox linked cell.
> > 
> > I've been previously using in the Workbook_Open procedure:-
> > 
> > Worksheets(Worksheets("sheet1").Range("c5").Value)
> > 
> > but the above code doesnt seem to work.
> > 
> > Hope you can help!
> > 
> > Cheers
0
Utf
4/12/2010 1:20:01 PM
This is the code i'm using that gets a runtime error application defined or 
object defined error:-

If ActiveSheet.Name = Worksheets("sheet1").Range("c5") Then
MsgBox "Already here on... " & ActiveSheet.Name
Else
'MsgBox "Not here!"
Worksheets(Worksheets("sheet1").Range("c5").Value).Activate
End If

Hope you guys can help!

"Martin Parker" wrote:

> Thanks that works great, however, one problem, if the user has previously 
> closed the app on the worksheet the code is referring to, I get a runtime 
> error on workbook open.
> 
> How would i code to check if the worksheet requested to open up at workbook 
> open is already activated?
> 
> Hope this makes sense!
> 
> Cheers
> 
> "JLatham" wrote:
> 
> > Worksheets(Worksheets("sheet1").Range("c5").Value).Activate
> > 
> > 
> > "Martin Parker" wrote:
> > 
> > > I have a combobox which is populated with all the worksheets within the
> > > workbook. The controlsource of the combobox is linked to cell c5 on Sheet1
> > > 
> > > My question is how do i make the Excel workbook open at the worksheet which
> > > is detailed within the combobox linked cell.
> > > 
> > > I've been previously using in the Workbook_Open procedure:-
> > > 
> > > Worksheets(Worksheets("sheet1").Range("c5").Value)
> > > 
> > > but the above code doesnt seem to work.
> > > 
> > > Hope you can help!
> > > 
> > > Cheers
0
Utf
4/12/2010 1:46:01 PM
I'd use something like:

Option Explicit
Private Sub Workbook_Open()
    On Error Resume Next
    Me.Worksheets(cstr(Me.Worksheets("sheet1").Range("c5").Value)).Select
    If Err.Number <> 0 Then
        Err.Clear
        MsgBox "Couldn't go to the sheet!"
    End If
    On Error GoTo 0
End Sub

There are a couple of reasons this could break.

You could have a bad name in Sheet1!c5 -- maybe the sheet has been deleted or
the value in the cell isn't legal (like a date mm/dd/yyyy) or you the worksheets
are named 1, 2, 3, ... and the value in the cell is numeric.

Or maybe Sheet1 doesn't exist anymore.



Martin Parker wrote:
> 
> This is the code i'm using that gets a runtime error application defined or
> object defined error:-
> 
> If ActiveSheet.Name = Worksheets("sheet1").Range("c5") Then
> MsgBox "Already here on... " & ActiveSheet.Name
> Else
> 'MsgBox "Not here!"
> Worksheets(Worksheets("sheet1").Range("c5").Value).Activate
> End If
> 
> Hope you guys can help!
> 
> "Martin Parker" wrote:
> 
> > Thanks that works great, however, one problem, if the user has previously
> > closed the app on the worksheet the code is referring to, I get a runtime
> > error on workbook open.
> >
> > How would i code to check if the worksheet requested to open up at workbook
> > open is already activated?
> >
> > Hope this makes sense!
> >
> > Cheers
> >
> > "JLatham" wrote:
> >
> > > Worksheets(Worksheets("sheet1").Range("c5").Value).Activate
> > >
> > >
> > > "Martin Parker" wrote:
> > >
> > > > I have a combobox which is populated with all the worksheets within the
> > > > workbook. The controlsource of the combobox is linked to cell c5 on Sheet1
> > > >
> > > > My question is how do i make the Excel workbook open at the worksheet which
> > > > is detailed within the combobox linked cell.
> > > >
> > > > I've been previously using in the Workbook_Open procedure:-
> > > >
> > > > Worksheets(Worksheets("sheet1").Range("c5").Value)
> > > >
> > > > but the above code doesnt seem to work.
> > > >
> > > > Hope you can help!
> > > >
> > > > Cheers

-- 

Dave Peterson
0
Dave
4/12/2010 2:07:04 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Opening publisher 97 with a later version
I have publisher 97 on my windows xp and it works fine. However, I had someone refine some work I had done and apparently they used a later version of Publisher because when I loaded their CD, I got the message "Publisher cannot load files from a different version" What can I do--does this mean I have to buy a later version to match the version he used, or do I have to buy one of those programs that can open other programs. If I do the later, will I be able to use my 97 version to make changes once I get the files open on the later version Thanks rjda Refer to http://www.mvps....

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Unable to open Outlook Express after upgrade to Win. XP
I upgraded from windows98 to windows XP Pro. Now when I try to use OE I get two error messages. "Outlook Express could not be started. The application was unable to open the OE message store. Your compter may be out of memory or your disk is full.....0x800c012e,3" "OE could not be started because MSOE.DLL could not be initialized. OE maynot be installd correctly" I found a similiar problem fix for window2000 which said to delete OE, rename the old folder, then edit the registry and reinstall. I did the above but was not allowed to rename the old folder and th...

code examples for BITS
Hi, I am looking for sample codes for programming background intelligent transfer service..I was able to see sample code in .NET, but I'm specifically looking for VC++ 6.0 (command-line application).Please help me.. Thanks, NKH ...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Opening an *.MSG file with it's respective attachment.
Hello, The file *.MSG is a message file. This can be viewed by any editor, like Notepad. However, when there's an attachment to it, we see garbage. Is there a way to open the attachments present inside the *.MSG files ? Thank you Yes, by opening it with Outlook. -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Create your own fully customized Toolbar -Creating a Classic View in Outlook 2003 Subscribe to the newsletter to receive news and tips & tricks in your mailbox!...

opening .pst archives
I am trying to open an outlook e-mail archive file (pst) that was made when I had Office 98 and transferred to my new computer (Office 2003 Pro). I am looking for an old e-mail that has now become evidence in a lawsuit. Can anyone help? You didn't say if the PST file was on a CDR/RW, hard drive, or network share. In any case, the PST file must be located on a local/network drive where you have full access (read, write, modify, delete, .etc) rights. Once the file is located in said place, you can open the PST file via File | Open | Outlook Data File. To close the PST file when you ...

Windows Server 2008 R2 04-09-10
Windows Server 2008 R2 and Windows 7 share the same code? how is that possible when Windows 7 has both 32 bit and 64 bit versions and windows server 2008 r2 is only 64 bit Hello Charle, As Microsoft is going to use only 64bit versions for servers they don't built the 32bit version. Sharing the same code doesn't mean that the server OS use exaclty the same files, there are a lot more and different ones. But the basic code is the same. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

conversion 04-07-04
anything change over the years.....can Filemaker Pro 4.0 be converted to Access ?? please reply on the Newsgroup, thanks Hi Cat, Not much has changed on this front. You have to export the data from FM into a format Access can read (e.g. CSV), import that into Access, massage the data into a relational structure, and finally re-create the functionality of the FM database using Access's native concepts and tools. On Tue, 6 Apr 2004 19:32:19 -0700, "Cat" <anonymous@discussions.microsoft.com> wrote: >anything change over the years.....can Filemaker Pro 4.0 >be con...

Office 2003 Service Pack 3--subsequent problems opening Publisher
I run Publisher 2003 on Windows XP. On June 13, I updated my system with Office 2003 Service Pack 3 so that I could open Word documents with the file ext docx. Subsequent to the Service Pack 3 installation, whenever I open a Publisher file (which I created), I get the following message: "Publisher has detected a problem in the file you are trying to open. If you are certain that this file came from a trusted source and does not contain harmful information, click OK." What is causing this and is there a way to stop this pop-up message? All publications? Error message when you...

Opening pub files created with older Publisher versions #2
I have just upgraded to Publisher 2003 from 2000 and am having trouble with pub files sent to me for our chuch newsletter which is using Publisher 97 The text is not wrapping around graphics boxes. Can I fix this? I really don't want to go back to Pub 200 Thank Richard this is caused by Publisher 97 not been printer independent. Even if you went back to Publisher 2000, unless you have the identical font versions and printer driver you would have issues with formatting. It sounds like the person sending you the file has a garbage HP inkjet printer. Get them to install the HP5P laser p...

Invoice Numbers 10-27-07
We produce reports that are invoices.. The reports are really a group of compined reports if this matters... When we print the reports I would like to have printed consecutive invoice numbers. If possible I would like to have the number apprear as AS-00001, AS-00002 ect.. I am not really interested in storing the invoice numbers I just need them on the printed invoice as it is made of of groups of various data that is stored... Thank In Advance for you help. Bob If you just want a consequetive numbering on the report, all with an AS- prefix, see: Numbering Entries in a Report o...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

opening a .docx with word 2004
Version: 2004 Operating System: Mac OS X 10.3 (Panther) How can I open a .docx attachment that has been sent to me with word 2004?? Can I convert it somehow at my end or does it have to be converted from the sender end? Thanks for any help... <cscs@sympatico.ca> wrote: > How can I open a .docx attachment that has been sent to me with word > 2004?? Can I convert it somehow at my end or does it have to be > converted from the sender end? Make sure that Office is up to date (or at least in version 11.5.0 - the altest version being 11.5.1) and install the XML convertrs you'...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

GP 10 AP Reconciliation Statement
Hi All, I have one query about the reconcile feature that is available in GP 10 to reconcile the AP and AR to GL. It has come to my notice when i take a recon statement for AP it does not match with the AP historical aged trail balance after taking into consideration the unmatched and potentially matched transactions. I have taken the AP smartlist for the given period and compared it with the transaction being displayed in the recon statement. What has come to my notice is the recon statement is not taking few transaction like invoice or payment for some reason. I faced this issue with almo...

Effectively stoping open relay.
I have a client who started using exchange 2003 few days back. This client is having more than 12 subnets starting from 172.27.50.0, 172.16.0.0 to 172.27.0.0 and 10.172.172.0 & 10.172.173.0 subnets in different location in town which are conneted by DSL. This users are connecting to the Exchange server 172.16.5.25. Most of these users are POP3 users. I have 2 SMTP Virtual Server running. Default SMTP Virtual server 172.16.5.25 and the second for Externel. In the Mail connector I have added only the external as the local bridgehead server. In the relay properties of the internal I...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Adding a combo box to a worksheet
Hi all, I'm re-creating one of our paper forms in Excel and I'd like to add combo boxes to some blanks on the form to allow the user to choose a name from a list. I know a little about Excel formulas and no VB code at all...what's the idiot-proof way to do this? Thanks, Chris Hi Chris, The easiest way is to right-click within Excel in the toolbars area and select the "Forms" toolbar. Then Forms toolbar should then appear and could can select the "Combo Box" icon and click on that. If you can't tell which icon represents the Combo Box, just hover yo...

Merge code issues with Publisher 2003
It seems whenever the data source is altered, the merge code fields have to be reinserted in the Publisher document. Is this normal? The data source starts in Excel and is then saved as a .txt file to retain number formatting on final merge. ...

SUTA state message on Pay Code Integration
I'm having trouble importing pay codes that are based on another code, such as Sick and Vacation. These records get rejected with the error message "pay code 'V' requires a SUTA state". I have a SUTA state in the Emp Maint window and the HOURLY pay code already exists and contains a SUTA state. I tried mapping the SUTA State code in my file and even tried using a Constant value in Destination mapping but these codes will not import. I can go into the Pay Code Maintenance window and add these codes manually with no problem. What am I missing? ...

MS Access 2007 code not running
I have created a brand new database in Access 2007 and linked some VB code to run on Form_Load. This code worked perfect with no problems the day I created it. Now today I opened the same form and it is not running the Form_Load code anymore (and yes it is still there, I did not delete it). I got frustrated so I copied and pasted the code to Form_Current and several On_Change events and set breakpoints at the beginning of all the events just to see if it was the Form_Load that was not triggering, but NO events are triggering my VB code. Any ideas? See this page: http://accessjunkie.com/fa...