Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










VBA To Hide "Insert Worksheet" Tab


Is there a way to hide the "Insert Worksheet" tab in '07? I would like to hide the tab so that users cannot add another worksheet to the workbook. I have tried the standard right click, but Excel will not allow you do that. I also dont want to lose visibility to the other worksheet tabs already created.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Insert A Picture That Is On Worksheet Into Another Worksheet
I have written a vba program that creates a report in excel. Most of the pages of the report are created at runtime. I have an image on a hidden worksheet that I need to place into one of the worksheets that is created at runtime. The image is an excel chart that I copied and pasted as a picture. I did this so that I could resize it easily and all of the elements of the chart would maintain their relative sizes. I have tried:

View Replies!   View Related
Hide A Worksheet
I want to know how do I hide my "Raw Data" worksheets? I have about 10 worksheets in a workbook and just need to hide the first one - where all the raw data is store...


View Replies!   View Related
Hide A Worksheet?
How to hide a worksheet? ... if I hide it will I it still be able to copy it?

View Replies!   View Related
Insert Row In 2nd Worksheet
I use the following code to insert a row of set parameters in one of my worksheets.

View Replies!   View Related
Insert One Worksheet Into Another
I was wondering if it is at all possible to insert one worksheet into another one? Specifically, I have a report that I have created, but someone requested a section at the top that would require completely different sized columns than the rest of the report uses. Is it possible to embed another sheet into this report so I can resize the columns without it affecting the rest of my report?

View Replies!   View Related
Display Worksheet Only - Hide GUI
I have an excel workbook that has multiple worksheets that is linked to our in-house system via ODBC and it refreshes every 5 minutes with up to date sales data.

I am looking to output just one of the work sheets onto a large LCD display / wallboard.

I need to just display the summary worksheet without the excel toolbars / gui


View Replies!   View Related
Hide Worksheet That Contains Over 15k Rows
I have a worksheet that contains over 15k rows, each row, in column AK has an input of X-123, x123, 123, ENVEL, ROL, WPL-503, etc. there are a total of 20 different inputs. These inputs are found on another file and inputted from with a Vlookup formula.

I tried advanced filter, but it is limited and thought, if I have a command button that opens a userform then I can select which ones I want to be hidden.

I don't know exactly if I should use a checkbox, listbox, combo box ect.

View Replies!   View Related
Hide Line In Worksheet
In excel I would like to create a macro which wil be able to hide all lines were a text (to be defined in a cell for example) is not present
Here is an example:

linesdata:X#1#2a#3b#4b#5a#6a#7c
Pending X value lines #2 to #7 will be hidden or not

View Replies!   View Related
Hide A Worksheet Within A Workbook?
We're trying to create an invoicing sheet with MS excel for our sub contractors but we also want the information they input to be transfered onto a second work sheet within the work book that has our mark up added.

Problem is that we don't want our sub contractors to see the sheet with the mark up. Is there any way to hide a work sheet or to limit access to the work sheets some one can veiw with in a workbook?

View Replies!   View Related
Hide A Chart Which Is Its Own Worksheet
To hide a worksheet upon open I use: wsSheet.Visible = xlSheetVeryHidden. What do I use to hide a chart which is its own worksheet?

View Replies!   View Related
Hide Worksheet Tabs
I've done a search for this but cant find the complete answer for what I'm looking for. Basically I've got a "Home Sheet" set-up where the user can only navigate to worksheets via the buttons on the home page (with a short- cut to go back to the home page). It works ok (very basic though) but the user can still use the worksheet tabs at the bottom of the screen to navigate (making this system void).
how I can, either, hide the worksheet tabs at the bottom of excel, or, a better way of navigating these worksheets (there is only about 6 of them).

View Replies!   View Related
Hide Sheet/Worksheet
How do you hide a sheet?

View Replies!   View Related
Lookup And Insert Into New Worksheet
I have got a worksheet called data with values in column M7 to M31 which are only there if they meet a criteria >0

if there is a value in cell M9 only, i would like to select the value of M9 and also the description in B9 and insert this into another worksheet called W1 on row C43 and i43 and repeat on C44 and i44 if values are >0 in M11, this would create 2 rows and would expand if there was more matches >0

View Replies!   View Related
Insert New Worksheet And Rename
Can I get a macro to insert a new worksheet each time it runs and rename it to a value held in a cell on another tab eg Sheet1, cell A1 - this value will change each time the macro runs so there wont be any duplicated tab names ?


View Replies!   View Related
Insert Images Throughout Worksheet.
Trying to find a code which will insert all images into specified cells.

[url]

All of the images are in a single folder C:/My Documents/Pictures

All are .jpg images which contain the prefix EV_

ie: image1 is called EV_10020003.jpg
image2 is called EV_40B00001.jpg
image3 is called EV_C003001.jpg

etc.

Throughout my worksheet I have cells containing text of an image
ie: in F23 contains the text EV_40B00001
in G50 the text EV_C003001

etc...

My aim would be to use a command button to search through my worksheet (Sheet1) and load up each image to their corresponding cell.

View Replies!   View Related
Insert Worksheet With Formulas
I have a workbook with a worksheet called Summary that has formulas that refer to other worksheets (say A, B, and C) in the same workbook.

What I want to do (with VBA because I have to do this many times) is copy this Summary worksheet into several other workbooks, all of which also have their corresponding worksheets A, B, and C.

I do *not* want the copied Summary worksheet's formulas to refer to the old workbook. If I copy the worksheet by hand, this is what happens.

Instead I want the copied worksheet's formulas to reference the worksheets of the workbook they are now in.

View Replies!   View Related
Hide Worksheet If No Macro Is Executed
I got two worksheets visible for user to enter information with vba codes behind the sheets(i.e. Private Sub Worksheet_SelectionChange(ByVal Target As Range). Most cells of the worksheets are protected if the vba is executed. However, it was found that if user chooses not to run the macro when prompted, the worksheet can be edited whatever user want. So, how can the worksheet whole worksheet is protected if macro is not run?


View Replies!   View Related
Hide Worksheet If Certain Cells Empty
I am trying to write a code that would hide when certain cells in the worksheet are empty. Also the sheet name should start with a -.

This is the code i have so far. The thing is that the sheet will always hide. The criteria on the cells doesnt work.

Sub Hide_all_filled_Templates()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If Left(ws.Name, 1) = "-" Then
If Not Range("I9").Value = "" Or Range("K9").Value = "" Or Range("M9").Value = "" Or Range("O9").Value = "" Then ws.Visible = False
End If
Next
Application.DisplayAlerts = True

End Sub
The code now hides all shees starting with "-". It does not take the cell criteria into account. Why?

Other point is that these cell references are just a few of what it should really be. How do I make this easier for myself to write the code. Point is that these cells come in row 9 (like the example) then in row 11 then in row 15, 17, 21,23, etc. Also the columns jump with uneven steps.

View Replies!   View Related
Hide Specific Worksheet Tabs
Is it possible to hide specific worksheet tabs.

I have a workbook with many worksheets and I only want to hide Sheet1 and Sheet2.



View Replies!   View Related
Hide / Disable Worksheet Menu Bar - !
Is there a way to either hide or disable the worksheet menu bar on ONE SHEET ONLY, so that a right-click customize doesn't even work on that sheet. I want the user to be able to do nothing but what my customized toolbar allows on that sheet.

View Replies!   View Related
Worksheet CheckBox (Hide Unhide)
I am having difficulties with this one. I have a button on my worksheet (sheet1) that runs a macro.

View Replies!   View Related
Hide Worksheet Based On The Value In A Cell
If Sheet1!A1 = FALSE, I want to hide Sheet2. If the value of Sheet1!A1 changes to TRUE, I want Sheet2 to be visible.

View Replies!   View Related
Hide / Protect Worksheet Code
I need to be able to save a copy of my spreadsheet but protect the structure so that the hidden rows cannot be opened by the person that I e-mail it to. At the moment I am using the current

Private Sub CommandButton2_Click()
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName
Call ProtectRobin
End Sub
Private Sub ProtectRobin()
ActiveWorkbook.Protect ("Robin")
End Sub

The saving part of the macro is working perfectly, however I cannot get the protection to work. It must have a password - I do not want the receipient to be able to go 'Tools' > 'Unprotect Sheet'.

View Replies!   View Related
Hide & Show Shape On Worksheet
I've been trying to make use of 'Run "doit", but the macro stops when it gets to my sheets called 'RST' and 'RST Pivot'. What I'm I doing wrong?

Sub DoIt()
Application. ScreenUpdating = True
With Sheet1.Shapes("Rectangle1")
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible)
End With
'Toggling sheets Forces Rectangle 1to show while code is running
Sheets("RST").Select
Sheets("RST Pivot").Select
End Sub

Also, do I need to change 'With Sheet1.Shapes' to reflect the actual sheet name?

View Replies!   View Related
Text Search Insert A Row On A Second Worksheet
Sheet1 has 500+ rows and 30+ columns of data, sorted by text in column G (last name). I want to create a data input sheet for users to manually key in data. I need help to create a macro to cut and insert the row from the data input sheet into Sheet1 - the first row after it finds a match in column G. The text in G can only be one of ten different "last names".

It needs to be after the first event because of graphing from the data sheet.

View Replies!   View Related
Insert Existing Worksheet Into Workbook
How do I insert an existing worksheet (previously exported from the same workbook)? I need to be able to do it programmatically.


View Replies!   View Related
Insert Text Box On Worksheet In 2003
how i insert text box on excel sheet.
In which i take values from user and then manipulate those values on click event of button.


View Replies!   View Related
Macro To Insert Rows Using A Worksheet Value
Need to look at a number in a cell, inserts that many rows below that row, then repeats this for following rows that also have cells that indicate how many rows to insert.

Attached is a simple sample data sheet with how data looks before and how it should look after.

View Replies!   View Related
INsert 1+ Column To Every Worksheet Of A Workbook
i have a workbook with over 100 odd worksheets of different names... i want to insert 1+ columns in every worksheet . For Example ...
in worksheet 1 name abc .. insert 1 column in column B (put clumn b data in column C)
in worksheet 2 name def .. insert 2 column in column B (put clumn b data in column d)
in worksheet 3 name ghi .. insert 3column in column B (put clumn b data in column e)

View Replies!   View Related
Insert Rows On Hidden Worksheet
My workbook contains 10 worksheets. Some users will use all 10 worksheets; some will use only 5 worksheets. If a user is not using a worksheet, I would like to hide it but leave it in the workbook.

However, I want to keep all workbooks in sync even though a user may choose to not use a particular worksheet. When my macros encounter a hidden worksheet, they stall. Does coding exist that allows a Macro to run on a hidden worksheet without making the worksheet visible?

View Replies!   View Related
Insert/Add New Worksheet When Cell Value Matches
I am trying to write/ find a macro that will open a new worksheet when Y is entered into a specific blank cell;

View Replies!   View Related
Insert Worksheet Macro Conditional
Still struggling with the calculator. Is there anyway to get a macro to insert a set number of worksheets. The set number would be the sum of two fields on Sheet 1.
Example attached. I have searched the forum and can now use a macro to insert a worksheet but have no idea how to tailor it.

View Replies!   View Related
Insert Row And Paste Data From Another Worksheet
The 'Add new material' button at the SMX sheet paste a range of formatted cells and formulas. The user is suppose to type in the description into the yellow filled cells. The problem is once the user enter sthe description in the yellow filled cells, i need the data to be copied into the FastCheck Sheet. Before that, a new row must be generated to copy for the data to be pasted.

View Replies!   View Related
Hide Command Button If Worksheet Is Protected
I want to hide a command button when a worksheet is protected and unhide when the sheet is unprotected.

View Replies!   View Related
Hide Column On Each Worksheet Generated By Macro
I have a macro that looks at a list of students and the school they attend, and then creates an individual worksheet for each school containing only their students. I want to hide Column B on each of those worksheets. This seems like it should be easy, but it is not working.

When I leave it as is pasted below, Column B does not get hidden.

When I change it to Sheets(school.Value).Columns("B").Select I get an error message stating "Select method of Range class failed."

Sub ExtractSchools()
Dim wsTransfer As Worksheet 'worksheet with transferred data from registrations wrkbk
Dim wsList As Worksheet 'worksheet with list of students
Dim wsNew As Worksheet 'worksheet being added for a school
Dim wSheet As Worksheet 'name to loop through all worksheets

Dim rng As Range
Dim school As Range
Dim rowNum As Integer
Set wsTransfer = Sheets("Transfer")
Set wsList = Sheets("Student List")
wsTransfer.Select
Range("Database_Transfer").AdvancedFilter xlFilterCopy, Range("Criteria"), _
wsList.Range("Database_Unique").........................................

View Replies!   View Related
Hide Rows On Multiple Worksheet Pages
This code will hide row 2 across Sheet1, Sheet2 and Sheet3 while recording in the macro record mode but when played back after recording will only hide row 2 on Sheet1. How do I get this code to work?

Sub MultiplePageHideRows()
Rows("2:2").Select
Sheets( Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Selection.EntireRow.Hidden = True
Range("A1").Select
Sheets("Sheet1").Select
End Sub

View Replies!   View Related
Protected Worksheet Macro To Insert Rows?
I have a protected worksheet, which I have 2 macros, 1 to spell check and 1 to insert rows, they both unprotect the sheet and re protect it again once they have completed. The problem I am having is that when I protect the work sheet first time round I tick the box to allow users to insert rows, once the Macros run they disable this functionality. Is there anyway I can include this in my Macros or do I need to add a new button!

View Replies!   View Related
Macro To Insert Column Heading To All Worksheet
Pls help me do a macro that upon clicking a button it will copy and paste a column heading (located in Row1 of Sheet1) to all non-empty worksheets (Sheet 2 to Sheet N) in the workbook simultaneously. Note that each worksheet has no column heading and has the same number of columns as that of the column header in Sheet1. Worksheet 2 to Worksheet N contains data extracted daily.

View Replies!   View Related
Macro - Auto Insert And Create A Worksheet
Is that possibe to use the "templeate" sheet and lookup the "Master" sheet to create a new worksheet called "result"

View Replies!   View Related
Insert A Line On New Worksheet With Copied Data
I need to add a line on the top of a new worksheet with the data copied from another worksheets cells. Using a macro. The line has to be created everytime on the top of the new excel sheet with the previous data moving one down.

View Replies!   View Related
Hide Multiple Rows When The Text Changes In A Cell On A Worksheet
The workbook has multiple sheets, and a cover sheet. Each project has a sheet and the status, costs, updates ect. are updated to the cover sheet.

Problem:
What happens is there is a cell which has conditional formatting on it, e.g. If text is Green then colour cell green, if cell text is amber then colour amber and if cell text is Red then colour Red. This works fine.

If the cell text is green then I need a number of rows hidden, if it is amber or red then show the rows.

I have found the following
Sub
If Target.Value = 1 Then
Rows(Target.Row).RowHeight=0
Else
End If
End Sub

Which I have changed to:

Sub
If F4.Value = Green Then
Rows("5,6,7,8,9,10.Row).RowHeight=0
Else
End If
End Sub

So this does not work, need to know why and how to make this work for each sheet I am on e.g. ActiveWorksheet?


View Replies!   View Related
Hide Certain Ranges In Different Worksheets According To The Assigned Cell Value In The First Worksheet
I have got a macro (from Mr Excel's MVP) that hide certain cells according to assigned cell value
What I need is:
to hide certain ranges in different worksheets according to the assigned cell value in the first worksheet

if sheets1.cell A1= "hi" then
range (" goooo") in sheet 1.hide
range ("deeeee")in sheet 2 .hide
range ("faaaaa") in sheet 3.hide
the macro I had is :
====================================
====================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value = "hi" Then
Worksheets("Sheet1").Rows(2).Hidden = True
Else
Worksheets("Sheet1").Rows(2).Hidden = False
End If
End If
End Sub

View Replies!   View Related
VBA Script To Hide And Unhide Specific Columns Within A Worksheet
Need the VBA script to hide/unhide specific columns within a worksheet.

View Replies!   View Related
Hide/Show UserForms Based On Workbook And Worksheet
I'm just beginning to work with userforms and have created a couple of forms for workbook navigation. The Menu form is set to appear only on the menu worksheet and the Navbar form is set to show on several database sheets. Both forms are set th hide when the workbook is deactivated.

Private Sub Workbook_Deactivate()
Menufrm.Hide
Navbar.Hide
End Sub

The problem is that when I return to the workbook the forms do not unhide. Using the workbook_activate event causes both forms to show simultaneously. I also have the worksheet_activate event set to show the applicable form.

Private Sub Worksheet_Activate()
Application. ScreenUpdating = False
ActiveWindow.DisplayGridlines = False
Navbar.Show
Navbar.Left = Range("B3").Left
Navbar.Top = Range("B3").Top
Application.ScreenUpdating = True
End Sub

Upon returning to this workbook, I only want the appropriate form to show relative to the worksheet that is active. That is, if I was on sheet2 when I switched to another workbook, sheet2 would be active when I return to the workbook and should show the Navbar form. The menu form should show on sheet1, and the navbar form on sheets 2-5. How do I get the correct form to show when I re-activate the workbook?

View Replies!   View Related
Insert Row In Worksheet One And Do The Same In Worksheet 2
I've attached a spreadsheet with 2 worksheets, "Summary" and "Detailed".

I would like to create a macro in "Summary" that enables me to insert a row at any position (e.g. insert row to Project 1 - 'above row 12'). While inserting the row, I would like to copy the formulas represented in cells "G:I" (e.g. G8:G11). Cell H11 and I11 both have vlookup formulas that lookup in "Detailed". However, upon inertion of row in "Summary", need to insert 25 rows in "Summary", while copying the cells G6:G31 within respective area of Summary.

My purpose is to set this spreadsheet up, so if anyone wants to insert a task or project to the summary area, they can do so with the click of a button and does not compromise any of the formulas.

Also note, I have code within the sheet that colours the cels within "Summary" "G:I" based on selections. I would like to ensure this is updated as well based on insertion of rows.

View Replies!   View Related
Show/Hide Worksheet Command Button Based On Cell Value Being In Another Range
i have a workbook with two sheets. i have a command button on sheet 1 that inserts another worksheet from another workbook based on a cell value in sheet 1. i would like this specific command button on sheet 1 to be hidden or disabled until a value is entered into a certain cell on sheet 1 that matches a value in a named range on sheet 2.

View Replies!   View Related
To Write A Macro That Will Insert A Formula Directly Into The Cells In Column M Of My Worksheet
I am trying to write a macro that will insert a formula directly into the cells in column M of my worksheet.

The worksheet has about 3400 rows. When I run the macro it does not adjust the cell reference in the formula so I am getting the same value in all 3400 rows.

I am using the following ....

View Replies!   View Related
Query Hide State Of Column Ranger And Hide If False
I want to create a macro that checks whether a specified range of columns is hidden, and

a) hide them if they are not hidden, or
b) unhide them if they are hidden,

i.e. toggle the Hide state.

What method can I use to query the hidden state of a selected range of columns and return a boolean value?

View Replies!   View Related
Insert Excel Formula Using VBA......MATCH Function Is What I Want To Insert
I can use the MATCH function when i am in excel but i am having trouble getting it to work when i am trying to insert the formula using VBA.

Here is the code i have but it wont even compile........

View Replies!   View Related
Hide Columns & Hide X-axis Labels
I am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.

Sub ShowA2()
Application. ScreenUpdating = False
num = Sheets.Count
Sheets("X-Axis").Activate
Range(Columns(1), Columns(256)).Select
Selection.EntireColumn.Hidden = False
For a = 1 To 5
Sheets(num - a).Activate
If ActiveSheet.Name = "A2 Data" Then
Columns("A:Q").Select
Range("A10").Activate
Selection.EntireColumn.Hidden = False
Sheets("X-Axis").Activate
Columns("A:E").Select......................

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved