Runtime Error Hidden Property - Macro Not Working In Protect Sheet

May 3, 2012

This first macro works fine:

Sub UnhideHBPorCholesterolQ1toQ3()
'
ActiveSheet.Unprotect "password"
Rows("58:67").Select

[Code]....

why the second macro does not seem to work. I get a Run-time error that says "Unable to set the Hidden property of the Range Class".

View 1 Replies


ADVERTISEMENT

Excel 2010 :: Runtime Error 1004 / Unable To Set Hidden Property Of Range Class

Jun 25, 2013

I am using this code (below) to hide certain rows or columns depending on what number is entered. Everything is working fine and I am at the point where I would like to share this excel sheet with others but I would like to lock certain cells so that others cannot alter the formulas. As soon as I lock the cells and then enter values into the unlocked cells, I get the "run-time error '1004': Unable to set the Hidden property of the Range class".

I am using excel 2010 x64.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("B3")) Is Nothing Then
If Range("B3").Value = 0 Then
Columns("G:P").EntireColumn.Hidden = True
Else
If Range("B3").Value = 1 Then
Columns("H:P").EntireColumn.Hidden = True

[code]....

View 3 Replies View Related

Runtime Error 1004 Unable To Set Locked Property?

Oct 27, 2011

I have a sub that I have copied out of a previous workbook where it still works fine. In the previous workbook it was assigned to a button. Now I am trying just to do it as an automatic action when the workbook is closed

It will lock any cell within the specified range that is not blank then protect the worksheet, save the workbook then save a dated archive copy

When I pasted it in to a new workbook I changed the range to A1:AP49 and strFileName

Now when I run it now I get an Error 1004 - Unable to set the Locked property of the Range class.

On Debug this line is highlighted:-
c.Locked = c.Value ""

The full code is:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' Lock_cells Macro
' Macro compiled 10/26/2011 by G CARNCROSS

' Locks used cells, saves the workbook then saves a password protected copy with the days date in the file name then closes the workbook
'
'
ActiveSheet.Unprotect Password:="SHES"[code].....

View 2 Replies View Related

Runtime Error 381 - Invalid Property Array Index

Apr 21, 2014

When I try to populate a worksheet with the data from a multicolumn listbox on a userform.

I have references which list index is to go to which cell on the worksheet, however if there is no entry in this list index it is giving me this error.

As the listbox could have 1 line or up to 10 lines populated, I think I need to put an error handling on so that if the entry in listbox index is "" then resume next, or something like that. However everything I have tried doesn't work and I keep getting this error.

Here is the code I have to populate the worksheet from the listbox. There are 7 columns and 10 rows.

How to handle this error?

[CODE]
Private Sub cbPrint_click()
Call Error_Handling_VBA_On_Error_Resume_Next
Dim ws As Worksheet
Set ws = Worksheets("Invoice Copy")
With ws
.Range("B11") = CusName.Value

[Code] ......

View 1 Replies View Related

Excel 2003 :: Runtime Error 438 - Object Doesn't Support This Property Or Method

Nov 1, 2012

I am using Excel 2003. I get the above error and when I press debug, the issue highlighted is the following:

Code:

graff:
adde.TextBox6.Text = "ok"
shet = ActiveSheet.Name
Set CurrentChart = ActiveSheet.ChartObjects.Add(5, 20, 350, 200)

[Code].....

View 6 Replies View Related

Default Property Of Protect Sheet Tool

Mar 13, 2013

I would like to know how to set default property of protect sheet tool to check the desired boxes. This because I have written the vba code to unprotect the sheet for some purpose and then protect back but after that there are only 2 boxes that ticked. I want to have another boxes to be ticked too.

View 6 Replies View Related

Password Protect Hidden Sheet?

Feb 28, 2013

Is it possible to have a hidden sheet password protected, so that only I can unhide the sheet ?

View 1 Replies View Related

VBA Lost Access To IE And Stopped Working - Runtime 438 Error

Feb 17, 2013

I'm scraping a website for some data using the following program. The program was working as expected until I decided to add a save file element that was a copy paste of some code a friend gave me (attached below). Funny thing is the save file functionality works but now I can't get IE to load the webpage - I get a 438 Error. I'm thinking there's a problem with something in the references menu.

Code:
Sub GET_PVI()
Dim Days_in_Month(1 To 12) As Integer
Dim Name As String

[Code]....

View 2 Replies View Related

Rewrite Protect Code So That It Will Work With A Hidden Sheet.

Aug 25, 2009

I need this code below to work in a macro on a hidden sheet.. FYI the sheet name is "Closed".

View 2 Replies View Related

VBA Code Not Working When I Protect The Sheet

Nov 9, 2008

I have used the below code to make hide/unhide some columns in my excel sheet. It works fine. Now, I need to protrect the sheet except the coloured cells. So I unlocked the cell by clicking the property of the colored cells & then did this :- tools- protection-Protect sheet - protect unlocked cells. But after protecting this sheet the macro code of hiding/unhiding of the columns are NOT WORKING & Errors is coming. I have used the below code:-

View 4 Replies View Related

Trying To Copy A Hidden Sheet Using Macro, Run-time Error "1004"

Aug 27, 2009

Basically my code is bombing at the red highlighted code. Error: "Run-time error '1004': Copy method of Worksheet class failed"

Sheet "Blank Form" is a hidden sheet. Can I alter the red section of code to work while being a hidden sheet? I tried a few variations but nothing seems to work..

View 5 Replies View Related

Hidden Worksheet Macro Not Working

Jun 26, 2006

saw the other posts about hidden worksheets and couldn't get my makro to work. The macro works fine when the sheet in question is not hidden but when it is or when I tried to solve it with

Sheets(" total").visible = True
.
.
.
Sheets("total").Visible = False

Why could this be? The problem only occurs when the sheet is hidden or when code like the above are used, what could be done to fix the problem.

View 9 Replies View Related

Runtime Error 1004 :: Cannot Rename A Sheet To The Same Name As Another Sheet

Apr 13, 2008

Why do I get the error: "Runtime error 1004: Cannot rename a sheet to the same name as another sheet, a reference object library, or a workbook referenced by Visual Basic"?

And how do I fix it? I have a macros that someone else made (thank you) and I need to make the macros create anywhere from 5-125 sheets based on the information added in sheet 1. How can I do this whe it stops me after 5 or so with the error above.

View 10 Replies View Related

Runtime Error When Closing The Sheet

Apr 20, 2006

This is a simple macro to hide the sheets if macro is disabled. But I get an error when I close the worksheet. Can anyone please help me with this. Other than that, everything seems fine. The problem is only when closing the worksheet if get an error as " run-time error '1004': Method ;visible' of object' _worksheet' failed". The code is given below.

Public bIsClosing As Boolean
Dim wsSheet As Worksheet

Sub HideAll()
Application. ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.CodeName = "TABLE" Then
wsSheet.Visible = xlSheetVisible
Else
wsSheet.Visible = xlSheetVeryHidden
End If
Next wsSheet
Application.ScreenUpdating = True
End Sub

Sub ShowAll()
bIsClosing = False
For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.CodeName <> "TABLE" Then
wsSheet.Visible = xlSheetVisible
End If
Next wsSheet
End Sub

View 9 Replies View Related

Compile Error In Hidden Sheet

Jan 14, 2009

I've written some VB code in Excel 2003 which hides and unhides worksheets dependant upon 'Yes' or 'No' in a named range. This works Ok in Excel 2003, however if I try and use it in Excel 2007 it shows error 'Compile Error in Hidden Sheet'. When I debug it the error 'Compile Error - Method or Data Member Not Found' appears. The code in question is as follows:

View 9 Replies View Related

Macro Runtime Error?

Jul 16, 2014

I have a bug on a macro that was working until yesterday. I did not write this macro and I cannot seem to fix it. The debug stops at the below command. The data it looks at seems to be in the right format so I think there is something else that is wrong.

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

View 3 Replies View Related

Macro Runtime Error 9

Dec 7, 2007

I downloaded an excel file from a German student's site. With this file, it is possible to calculate very simply species turnover (I'm a biologist) activating a macro. When I run it, afetr answering lots of questions (which I think I answer well), it appears an "error run-time 9 - subscript out of range". In the download folder there were only two files: turnover.xls and instructions.pdf
Here is the code. The debug highlights the bold line.
.............................................

View 9 Replies View Related

Runtime Error 1004 When Copying And Pasting Data To Another Sheet

Apr 17, 2014

I'm generating a runtime 1004 error with the macro I have attached to CommandButton1, "Clock_In".

The macro is copying the name, date and time from 3 cells on Sheet1 and pasting special values to another sheet.

View 2 Replies View Related

Copy Non-Blank Data From One Sheet And Paste To Another - Runtime Error 424

Feb 14, 2014

I have a very basic code to copy "non-blank" data from one sheet and paste to another. The code is not complete yet - I am running in debug mode I get the above error. My code is as below.

Sub SampleFind()
Dim StrWord As String
Dim Quantity As String
Dim i As Long

Call nrows

For j = 2 To 2
For i = 2 To nrows1

[Code] ..........

View 4 Replies View Related

When The Sheet Is Protected It Ends Up Throwing A Runtime Error ( 1004 )

May 9, 2006

I am currently working on a project which at various times does 2 different things. These things are to be done on a "PROTECTED" (UserInterfaceOnly:=True ) worksheet.

The first is a Range.Replace command.
The other is a Hyperlinks.Add command.

Now when I'm testing ( typically the worksheet is not protected ) everything works like a champ. But as soon as I protect the sheet the following scenario happens.

1. The replace DOES NOT work.
2. The hypelink.add DOES NOT work.

when the sheet is protected it ends up throwing a runtime error ( 1004 ) "Protected Sheet error" the real odd thing about this is that it doesn't throw the error on the command itself. On the hyperlink the error happens about 3 command further along in the flow............

View 5 Replies View Related

Compile Error In Hidden Module In Sheet 1 In Excel

Dec 31, 2012

When I open attached excel file excel show message that," Compile error in hidden module in sheet 1". and file stop working. this error occur in only in Pc. How I remove this error.

View 1 Replies View Related

Form Controls - Save Property Changes Set At Runtime?

Jun 30, 2014

Imagine I have a form with a few controls on it. And the controls properties are set up at runtime.

Is there any way possible to save the property changes that were made at runtime to the controls themselves? I mean - short of manually editing all the controls at Design time?

This might be easier to explain by example. See dummy code below. Using this example - I want to find something that will save the Caption of CommandButton1 as "TestMe" (rather than have it only temporarily set at runtime).

View 2 Replies View Related

Insert Static Timestamp But Receive Runtime Error When Deleting Rows In Sheet

May 20, 2013

The code below works nicely to insert a time stamp in column B when the user selects "Yes" in the corresponding cell in column A. The problem is if I try to delete any rows or clear the contents of the cells I receive a "Run-time Error 13 - Type mismatch." If I delete the contents of the cells one at a time I do not receive the error. What I am trying to do is the user has a list of items to select from in column A. Only if "Yes" is selected I want the time stamp in column B to appear.

Code is below.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

With Target
If .Value "Yes" Then Exit Sub

If Not Intersect(Range("A1:A50"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 1)

[Code] ......

View 6 Replies View Related

Runtime Error (1004) For Inserting Formula Macro

Aug 21, 2014

I manage to find a macro online, which aims to insert a desired formula into desired cell range. However, when I execute the macro, i get a "run-time error, 1004".

View 1 Replies View Related

Macro Runtime Error - Items (charts) Not Found

Oct 3, 2013

I've got an interactive chart on a sheet where users select a chart from a combo box control and a picture link to the chart is displayed. The actual charts are on a hidden sheet. I have to activate the charts first for it to work so I put a button the sheet and recorded a macro where I unhide the hidden sheet, select each chart as I scroll down the screen by clicking on it then hide the sheet again and return to the interactive chart.

My problem is that I'm getting an error when the macro tries to select the first chart which is called "Chart 6":

Run-time error '-2147024809 (80070057)':

The item with the specified name wasn't found

I recorded the macro so I'm not sure why the macro isn't working now.

Code:
Sub ResetCharts()
'
' ResetCharts Macro
' Reset all charts
'
'
Sheets("Budget v Actual Graphs").Select
Sheets("Graph BG").Visible = True
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 35").Activate

[Code] ............

View 5 Replies View Related

Macro Error After Protect File

Jan 4, 2010

I created excel form with calculated formula and macro. Everything worked fine. To protect spreadsheet formula I protected file. But after protection when I ran file I get macro error message. Then I remove protection become work without error messgae. How to fix that problem?

View 6 Replies View Related

ComboBox Error "Could Not Get The List Property - Invalid Property Array Index" When Typing Out Of Range

Jun 9, 2006

I have a form with several combo boxes, and they function just the way I like as far as being able to pick from the list, or typing in them and having it show you the next available item in the list as you add letters. Whats happening that I would like to know how to deal with is... as soon as you type a letter that is not in my lookup range it generates an error. "Could not get the list property - Invalid property array index". I don't want people to be able to add to the list, but I would like a msgbox to pop up. Then allow them to go back to the box and try again.

View 2 Replies View Related

Save File With Hidden Property

Jun 9, 2007

I have an excel file which is in windows explorer has its property set to hidden.

I have another excel file, with some Vba in it, which opens the hidden file, inserts some data into it, saves the file and closes it.

ActiveWorkbook.Save
ActiveWorkbook.Close

This all works, except that the hidden property is lost in the process.

Is there a way of saving the file, and keeping the hidden property? This is just so that users cant see the file and accidently open it.

View 3 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

Excel 2007 :: RunTime Error When Running Macro From Alternative PC

Aug 9, 2012

I am getting a "Run time error 5", "invalid procedure call or arguement" when I run a macro on a PC other tha the one on which it was created.

In this case both PC's are running on the version 2007 of Excel.

This is the highlighted statement when the macro fails

Code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"SOCX25!R1C1:R" & Lr & "C23", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="SOCX25!R7C25", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

View 4 Replies View Related







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