Visible Sheets To Be Hidden With The Exception Of The Sheet Called "open". Then Is Saves The File

Sep 9, 2008

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.

View 9 Replies


ADVERTISEMENT

Format All Cells In All Sheets To Protection Hidden On Visible And Hidden Tabs

Feb 28, 2014

I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.

View 7 Replies View Related

Spreadsheet Open But Not Visible. Hidden

Nov 8, 2006

I was working on a multi-tab spreadsheet (7 tabs to be exact) and I went to hide one of the tabs and all of a sudden the worksheet went away and I was left with my empty personal.xls spreadsheet. If I go into tools and visual basic I see my spreadsheet listed as VBAProject (Durex Sales Oct 1 to Oct 28 06.xls) with the 7 sheets listed below it within a folder called Microsoft Excel Objects, but I can't get it back in spreadsheet form.

View 5 Replies View Related

Hidden/Visible Sheets In Listbox

Sep 22, 2009

I'm trying to create a userform that allows the user to swap sheets from visible to hidden status. I've made it to the point where all the sheet names are populated based on their current status in their respective listbox, but I am stuck on using the results of any swaps to newly set their visible property. the code I have in the userform is as follows:

View 2 Replies View Related

Keep Hidden Sheet Made Visible As Active Sheet

Mar 2, 2007

I have a button on a sheet that runs a macro to unhide another sheet. That works, but I want the sheet made visible to remain forward. Instead, the button unhides the sheet and the sheet the button is on comes forward again. I am unable to figure how to keep the sheet made visible forward. Here is the macro 'as recorded'.

Sub UnhideSheet1()
Sheet2.Visible = True
End Sub

View 7 Replies View Related

Open A Workbook With Only One Sheet Visible.

Mar 10, 2009

How do I get a workbook to open with a selected sheet visible, or preferably all hidden? I already have a userform that opens upon opening the workbook that the user 'Enters' the workbook with.

My untidy method would have been to use an If -Then statement for each sheet in the Private Sub Workbook_Open. Is there a neater way of doing it?

View 6 Replies View Related

Open Hidden Sheet With Clicking Button?

Aug 23, 2013

I have a workbook including several sheets.

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.

View 12 Replies View Related

How To Have Open Hidden Sheet / Hide On Closing

Feb 24, 2014

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?

View 6 Replies View Related

Automatically Check For Existence Of Hidden Sheet Upon Open

May 1, 2009

I use the code below to check and see if a sheet exists or not when a workbook is opened. If it does not, continue. If it does I need to run a different sub on it.
Twist, I need to check for a hidden sheet. How would I chg the code to do this?

Private Sub Workbook_Open()
'Also need to check the code in the userform. Has On.Time command.
'SplashForm.Show
Sub Sheet_Test_1()
Dim sh As Worksheet
On Error Resume Next
Set sh = ActiveWorkbook.Sheets(" total")
If Err.Number <> 0 Then
MsgBox "The sheet doesn't exist"
Err.Clear
On Error Goto 0
Else
MsgBox "The sheet exist"
End If
End Sub

View 5 Replies View Related

Importing CSV File To Hidden Sheet Without Selecting Sheet

Mar 14, 2014

I have this code that first selects a sheet (data) and then imports a csv-fil to the data sheet.

I want to hide the data sheet and the the code doesn't work.

How do I change the import code so that I do not select the sheet?

[Code] ......

I believe it should be something like this: With Sheets("DATA").QueryTables. Add..... but I am not aware of the syntax.

View 1 Replies View Related

Macro That Saves Current Open Workbook To Directory Specified?

Jan 10, 2012

I would like a macro that saves the current open workbook to a directory specified in a cell on the open work sheet.

View 4 Replies View Related

Importing Text File And Getting Sheet Hidden

May 24, 2006

Need Macro where I can import a text file saved on my computer into a new sheet named yesterdays or todays date in format "ddmmyy" and then the sheet gets hidden.

View 9 Replies View Related

Formula Error In Hidden Sheets With Formulas Linking To A Sheet Of Same Workbook?

Nov 30, 2012

I am currently working with a hidden sheetname "hide_template" which contains formula linking to another sheetname "rawdata" of same workbook.

In this "rawdata", the contents is updated from time to time.

The problem is when I open this workbook and update the entries in "rawdata", the formulas/results in "hide_template" is not updated and invalid (ie. #N/A, #VALUE..)

This is the overview of process that Im working with:

===> open workbook ===> paste new entries on "rawdata" ==> run a macro to copy range in the "hidden_template" to paste to new sheet of same workbook

I already have a working macro to copy range from the "hidden_template" which works as follows:

===> set to visible the "hidden_template" ===> copy range from "hidden_template" ===> paste to new sheet ===> set to hidden the "hidden_template"

View 1 Replies View Related

Little Data Saves As A Large File

Jan 9, 2008

I have some spreadsheets one worksheet little data and it saved as it a 5MB file when it should only save as something like 500Kb, what would cause this?

View 3 Replies View Related

Tab Delimited File Saves Commas As Dot

Jan 30, 2008

I'm using Excel 2000 swedish version, that is we use comma to seperate decimals not dot. I have a macro that saves a sheet as .txt file (tab delimited). However in the sheet (journal voucher) I need to enter a couple of numbers all with 2 decimal, that is 5 is 5,00 and so on. But then, when I save the sheet to a .txt file using a macro all the commas are saved as dots and since I'm importing the file to SAP it will not accept the dots. Is there some way ( changes in macro) I can prevent the commas from becoming dots when I save the .txt file?

The save macro is the commomly used

ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settings& strText & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate

View 4 Replies View Related

Automatically Open File In Specific File Path When Another Sheet Is Opened

Mar 20, 2014

I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls

So opening x.xls will automatically open y.xls

I've tried this in the workbook code area but it doesn't do anything.

[Code] .....

View 1 Replies View Related

One Sheet Saves A Column To The 1st Empty Row On A 2nd Sheet After 1st Looking For A Match On An ID Variable

Apr 2, 2008

I am trying to create a macro so that when i save an entry the form sheet looks in the tavela master sheet to see if there is a match on the record number (in the A column) and saves there as an update if it matches and then if there is no match, saves it in the last empty row. this is a column to row copy so it has to be paste special to transpose. also i have a password on the sheet which i use to unprotect then reprotect. i have successfully done these two things separately but cannot combine them! i know not very much about visual basic code just what i have read here and so i am trying to make this code work.

Code:....

View 9 Replies View Related

LastRow Needs To Select Next VISIBLE Row (not Hidden Row)

Jul 19, 2007

I'm having a problem moving the cell cursor 2 rows down once I find my LastRow (see LastRow code below). My problem is I need to move down 2 VISIBLE Rows down and I'm running into it moving 2 Rows down whether they are Visible or Hidden? If Row 50 is my LastRow, and I have it move 2 rows down, if Row 52 is Hidden, it still moves to Row 52 which can't be seen by the user after I do my stuff to that row? I can't unhide any hidden rows for other reasons. Is there a way to have the cursor move down 2 VISIBLE Rows instead of 2 Rows regardless?

The code I use to select the LastRow is:

LastRow = Cells(4000, 8).End(xlUp).Row
I move down 2 more rows using:

Cells(LastRow + 2, 8).Select 'Then I do some stuff here for the user, so it can't be a hidden row

View 9 Replies View Related

After Macro Saves File Msgbox To Confirm Save As Completed

Jan 18, 2014

I have a macro which works well. All it does is save a file to a specified location on my network. However, after a file is saved there is no prompt telling the user that the file was saved successfully leaving them wondering whether the macro actually worked.

VB:
Public Sub SaveAs() ThisFile = Range("X2").Value
ActiveWorkbook.SaveAs Filename:="legdcAudits" & ThisFile & ".xlsm", FileFormat:=52
End Sub

View 1 Replies View Related

Saves A Template With Macro (XLTM) As A Normal File (XLSX)

Sep 10, 2009

When I prepare a file with macro's (to be used by other people), I save this file as a template with macro's with the extension *.xltm (template with macro).
Therefore no one can overwrite my file. These other people can open this template (e.g. double-click, but not file-open). But when these people save this file, Excel automatically saves it as a normal Excel-file with the extension *.xlsx.
Ok, a message is seen after pressing <enter> or clicking Save, but does normal users know, what they have to do then?

If I'm making a template with macro's, I want Excel to have this file saved as a file with macro's. Whatever did I make a file with macro's for? Is this a bug or is it done on purpose?

View 3 Replies View Related

Excel 2007 :: Macro That Saves To A Specific File But Has Spaces In It

Jun 24, 2013

I am using Excel 2007. I have pasted my code below. It works fine unless it has a space, in which case it inserts %20. In this case, sRange2 = ActiveSheet.Range("E11") will always have a space in it since this is where i store a job's name.

Sub Macro1()
Dim sRange1 As String
Dim sRange2 As String
Dim sRange3 As String
Dim sRange4 As String
Dim sFullPath As String

[code]....

View 4 Replies View Related

Toggle 2 Subs (between Either Hidden Or Visible Menus)

Dec 11, 2013

I would like to toggle (button) between either hidden menus or visible menus. I have the following code:

Private Sub ToggleButton1_Click()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual

[Code] .......

Will not work. either one state or the other is visible.

View 2 Replies View Related

Excel 2010 :: Right Click (save As) Saves Inoperative XLSM File

Mar 22, 2012

Excel 2010, Win 7. I have attached a .xlsm file to an email. The contained macros run perfectly, and one of them is "public" so it shows up in "Developer - Macros", and "Customize Quick Access Toolbar" lists.

If the recipient clicks and drags the attachment to a folder, or copy/pastes the attachment, it works fine. (Macros are enabled, etc.)

However, if the recipient right clicks on the email attachment, and selects "SAVE AS" from the item list, the file saves, has the right name and extension, looks ok (the icon has the exclamation point, etc), and it is the correct size, but it simply will not load. You can double click, do a file open, etc. but it will not load. It's a hidden workbook, but if you "open it" and Alt-F11 to show the VBA editor, it isn't there!

It's not stopping the project - we simply tell them to click/drag, etc. but I totally do NOT understand why that happens.

View 5 Replies View Related

Change Textbox Border Color - Visible Or Hidden

Mar 8, 2012

What command I would use to change the text box border color, and set text border to visible or hidden?

View 3 Replies View Related

ActiveWorkbook.SaveAs Only Saves One Sheet, Not Whole Workbook

Aug 2, 2006

I have a csv .txt file that I am importing into Excel, and then creating a pivot-table from that data. This results in 2 tabs within my workbook, pivot-table and data. Using the following code does everything I want it to, but when I re-open the saved .xls file I only have the pivot-table sheet. I'm specifying to save the full workbook, aren't I?!?

Sub format_bgas_flowtracer()
Dim v_excel As Excel.Application
Dim v_worksheet As Worksheet
Set v_excel = CreateObject("Excel.Application")
On Error Resume Next
Kill "c:oh_for_gods_sake_JUST_WORK_YOU_STUPID_SODDING_THING.xls"
On Error Goto 0
v_excel.Workbooks.OpenText FileName:="D:gas_flowtracker_out.txt", Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _ ...............

View 4 Replies View Related

Debug Function Called From Another File

Jun 26, 2007

I have a macro that calls functions from another workbook.

For example:

Private Sub Worksheet_Change(ByVal target As Range)
If ThisWorkbook.OpenCommon <> -1 Then _
Application.Run Workbooks("common.xls").name & "!Arkusz1.CellChange", target
End Sub

But when I get an error in CellChange function from "common.xls" workbook, debugger points only to the third line of the code above, not to the bad line in called function.
Is there any way to change this behaviour (maybe some tool etc.)?

View 5 Replies View Related

How To Close Text File That Was Opened By DLL Called By Excel

Mar 11, 2014

I call it from a macro. It is supposed to write some temporary files, and close them in the end, and delete them. Sometimes, the dll can't close properly, and the text files remain open.

With error treatment in my macro, I can continue execution, but i need to delete that files.

Since the dll DOES NOT CLOSE the files, i'm not able to delete them in my macro.

I need some way to close that files, in order to delete them.

When I use GetFile to reference the file, it remains as "File", which does not accept "Close" method. I can reference the file with myfile.OpenAsTextStream, but this way it seems to me that the file is opened again, and the "Close" method does not work either, with the file defined as a TextStream object.

If I close excel, I'm able to delete those files, but that's not the way I want. Since IT'S OPEN IN EXCEL PROCESS, I imagine there's some way to close it.

View 1 Replies View Related

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

View 1 Replies View Related

Open File To Specific Sheet Other Than Last Save

May 15, 2007

If i have a sheet with multiple tabs can I somehow make excel open that file on a particular tab rather than the tab that the user was on when they saved/closed it.

View 9 Replies View Related

Open File Where Path, Name & Sheet Name Are Variables

Jan 18, 2008

I'm trying to use vba open a new workbook and then edit it. But i can not select cell in the new workbook. but it can be activated!

it can open the file (fname) and activate the sheets (sName), but will flage an error on range select.


Workbooks.Open fileName:=fPath & "" & fName
Workbooks(fName).Sheets(sName).Activate
Range("A8").Select

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved