Open Formula Sheet And Hide All Sheets Except Clicking Hyperlink
Feb 22, 2013
I have a file having around 57 sheets.But here,I have taken a example.I want a file whose show me only summary sheet.But when,I click the cell no.C7 then the related sheet should be open i.e.FNDADRSCC,and all the sheets should be hide.I have little knowledge of HYPERLINK formula but not to the desired stage.
For more clarification, refer attached file.i.e. BOM Sheets
Desire result file have attached also i.e. Excel_formula
I'd like to hide all worksheets and in the excel there is only one visible sheet which is Navigation Page. I want to add several buttons on this sheet and each button will be clicked to call the other sheets.
For example, there is a sheet called Sales_Approval_Form and I'd like to add a button on navigation page called Sales Approval Form and when the user clicks the button the sheet will be open for entering data.
I have a workbook that has worksheets that serve as data input pages for the user. I have control boxes created by the forms toolbar.
I created a splash screen that is to be launched when the file is opened. Instead, often the last sheet that the user was on when they last saved the file (usually but not always the worksheet called "Main Menu") is seen first.
I understand that not all the sheets can be hidden - that one must be unhidden.
So I created a sheet called "Background" that is a solid color with no gridelines.
Objective:
When the file is opened, I want the "Background" sheet to be seen first and then the splash screen to be initiated.
Now - when the user opens the file, the Main Menu worksheet is seen for a few seconds (or whatever the last sheet was on before it was last saved), then it disappears and the solid background screen is shown with the splash screen. When the splash screen ends, the "Background" sheet disappears.
Here is the code that I have in the workbook
Private Sub Workbook_Open() Dim wks As Worksheet On Error Resume Next For Each wks In ThisWorkbook If wks.Name "Background" Then wks.Visible = False End If Next wks End Sub
What has to be changed so that the first sheet that is seen upon opening the file is the "Background"; then the splash screen happens; then it returns to the Main Menu sheet?
I have a lot of text in a cell and I want to create a Hyperlink using only 2 or 3 words and not the whole text. Unfortunately, it seems as though the hyperlink can only be from the cell itself. Is it possible with Excel to create a Hyperlink from a section of text in a cell, and NOT from the cell itself?
In my excel file, I have a cell with a hyperlink which points to an intranet portal. When I copy the hyperlink and paste it in the browser's address bar, the site opens. But when I directly try to open from the excel, by clicking on the cell, it redirects to a wrong URL and eventually does open the site.
I have created a workbook of 12 monthly worksheets, with an index page with hyperlinks to each worksheet. There is also a hyperlink on each worksheet to return to index page. Each worksheet is protected, but the cells which users can enter information are not locked. The protected worksheets have the 'select locked cells' unticked.
I have found that an inadvertant click on any of the protected cells in the worksheet will take users back to the index page. This is very annoying, what have I done wrong? The hyperlink is one of the unlocked cells.
This is activated by using a button on the BCDashbd Page. I want it to hide the BCDashboard sheet and Unhide the Main DshBd Sheet. Currently it opens the Main Dshbd sheet but does not hide the BCDshboard Sheet?
I've got this function where you can select from a list of names, and when you press go it unhides the sheet and goes to it, problem with that was after closing the sheet it was no longer hidden. So now, I have
[Code]......
So when you press go, it unhides the sheet, goes to it, and hides it again, but then the sheet is hidden it pushes the user back to the page they was on, so it would appear you can't have a hidden sheet open.
Is there a way for excel to determine when the sheet has been closed, to be able to hide it when the user navigates away?
I wanted to force the user to "enable macros." Searching online brought me to the method of hiding all sheets but one and only un-hiding the important sheets if the user accepts macros. This is done with event procedures as shown below ( I stole this code from a webpage and only added unprotect/protect workbook and made the Hideall and Showall subs private also since I don't want the user to have any idea what's going on):
The code below starting with BEGIN works but my problem is I need to add some additional stuff to the WorkBook_BeforeClose procedure. What I added to the BeforeClose procedure is a series of IF statements that follow this form:
If IsEmpty(Range("C9").Value) = True Then MsgBox "Please enter data in this Cell C9", vbExclamation + vbOKOnly, "Validation" Cancel = True End if The problem occurs if the User attempts to close without entering the required data. This will correctly trigger the above IF statements as desired. However, after Canceling, if the user decides to Save (without closing, by say clicking the floppy disk button in the upper left), then "HideAll" is executed which I do not want. I assume this is because bIsClosing has been set to TRUE and was never reset after the IF statements triggered a Cancel? Is this correct? How can I fix this? I tried adding a bIsClosing = False line at the end of every IF statement, but I think that just made things more screwy.
BEGIN
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Cancel = True Or bIsClosing = False Then Exit Sub Run "HideAll"........................
I am making a new stock card system to keep track of stock at work for a new storeman working in my department. What I want to be able to do it hide/show sheets bases on their colour, the reason I want to do this is because I want the new storeman to be able colour code products and only show certain ones at any one time to stop him from having to sort through loads of sheets, but also for him to be able to add extra sheets and asign them a colour and not need me to modify code and put it into category X So in summary, I want code that will hide all sheets that colour is red or blue etc and not need updating when new sheets are added.
There is 2 parts to my question and I will explain them as best as i can.
1. I have a front sheet and 12 other sheets ( 1 for each month of the year) they all have the same layout. On my front sheet I want to create a button which will capture the sheet onto my front sheet for the current month, whether this means to make 12 buttons to choose which month is displayed on the front sheet or one that detects the date by system time i dont mind.
2. These 12 sheets will ideally be hidden and what I am wanting is again, on the front sheet 12 buttons for each sheet to bring up the corresponding hidden sheet so they can be viewed, and then on each of the 12 sheets another button which will hide them and return the user to the front sheet.
i use excel 2007, it s slow to open a file by double-clicking and entering from windows explorer, by googling i fould a fix by adding "%1" at the end of command in "Application used to perform action" (folder options/file types/xls/advance/open). however, to open files in the same instance, i have to keep "DDE message" with [open("%1")]. With both "%1", excel now opens twice. How to fix it?
I want to create a hyperlink to a sheet named "adsf"
I am currently in a worksheet named: "62b Arcus"
I want the hyperlink to be set by grabbing the name from another cell.
For example, In cell h7, I have the text: adsf
In cell g7, I want to place a formula such as: =HYPERLINK("adsf!")
Except, instead of this, I want: =HYPERLINK("h7!")
In this way, i want it to hyperlink to a sheet name based on the text that is in h7.
But neither of these formulas work. Both say the following: "Cannot Open the Specified File"
After reading up on this I have discovered that I must save the file and include the file name inside the formula.
My file name is: [Maintenance Color Codes of Houses - colour coded2.xlsm] =HYPERLINK("[Maintenance Color Codes of Houses - colour coded2.xlsm]adsf!A1","LINK")
This hyperlink actually works. Yet I have a problem. What if I rename the file. For this reason, I want it to grab the current file name using "filename". I have tried this by the following:
I know that I have probably created a ridiculous formula for what I am after. I'm almost there but not quite. You may know something far, far more simple.
I run into is that many of our staff don't know there are tabs at the bottom of the page that show different worksheets. writing some VBA code that would grab the names of all the sheets and create a single sheet with the tabs listed as buttons that would hyperlink to each sheet of it's given name. Kind iof an index for the sheets.
Also bonus if 3 rows could be insterted on each of those pages with a button that links back to the index page.
I am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
I want to make it possible to refresh a particular sheet on clicking a button. i can create the button and even assign the macro. but the code for macro, to refresh i don't know how to generate the code.
I have a problem and i believe someone else has this too. I have an excel file which contains near 600 hyperlinks to different websites. But my problem is i cant open more than 1 hyperlink at the time and in that way going trough 600 websites would take like 4days, is there any program / Software, plug in, or ANY way open Many hyperlinks same time so i dont need to click 1 after 1?
I tried
Sub OpenUrl() Dim r As Integer
r = 2 Do Cells(r, 1).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True r = r + 1 Loop While Cells(r, 1).Hyperlinks.Count End Sub
I have a VB macro script which needs to open a hyperlink. The hyperlink is a file on C: itself, but everytime I try to open the hyperlink, I get a safety prompt which confirms whether or not I want to open the hyperlink as they can be suspect. I need to know how to disable that prompt as that halts my macro right then and there. Does anyone know how to go about disabling this prompt message?
I need to post an Excel file to a web site that is sort of a "navigation" tool. What this tool will do is open a series of Excel files that are also on the web site, print something in them and then close them. My files have multiple worksheets, but if I use the OPEN command, my workbooks only have one worksheet (obviously, the first). So, I tried using the hyperlink(1).follow command which works and opens the file in its own window, however, as soon as the file opens, it has the focus and my macro in my navigation file stops and everything comes to a halt.
I have designed a software in excel/vba and when I run that project then startup form appears which has various buttons on it and they perform different functions.
My problem is when I click on button "View MI Report" at first out of various buttons then it works fine and displays me the excel chart sheet. But If click on any other button first and then on view MI report button then startup form disappears and a file is created at the desktop and then after few seconds startup form again appears but it doesn't display the excel chart sheet. I have written the following code on the button
Private Sub CommandButton5_Click() For Each wssheet In Worksheets If Not wssheet.Name = "chart1" Then wssheet.Visible = xlSheetVeryHidden End If Next wssheet
I have one issue with my protected worksheet. Some locked cell contents are linked to cell contents located in another sheet. When I double clicked on these cells, a warning window poped up but also opened the sheet in which the corresponding cell is located. This is quite annoying to have to go back to the former sheet. How can I prevent it ?
i am using the macro below to get excel to hide all sheets but the one called "open"...
PHP Sub savemini()
ActiveWorkbook.Unprotect "letmein"
For Each sh In ThisWorkbook.Worksheets If Not sh.Name = "open" Then sh.Visible = xlVeryHidden Next sh
ActiveWorkbook.Save End Sub
So the workbook is protected with the password "letmein", and the VB code above in theory should unlock the workbook, allowing all visible sheets to be hidden with the exception of the sheet called "open". Then is saves the file.
Now heres the odd bit... it works for certain people, but not for others. I have made sure those it wont work for are not doing anything weird and they are not.
The workbook i am using has a code that only opens certain sheets for certain users. I as a master user have access to all sheets. I can go into the users sheets and click the button that activates the macro above myself and it works fine, but for some users it wont work.
The text displayed in the cell is a postcode and the link takes you to the Postcode location in Google Maps. I have tried linking this to macros & creating a hyperlink on the button using the same code but I am having no joy. Is there a simple way of activating this using a macro linked to a shape?
Above is the formula that I have, I am putting it into Column L. When I do auto fill every row that has data in J and K the formula goes into as you would expect.
However the formula does not activate until I double click inside each individual row and hit enter. Therefore if the calculation in L4 was 1.0, then 1.0 will appear as the calculation in every row of Column L until I double click in each row individually and press enter.
