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
ADVERTISEMENT
Jul 10, 2014
I use Excel 2010 andexample.xlsx cannot make my code working because of the so famous errore above.
I have several pivot tables (pt) in different worksheets (ws), and a list of items stored in an array I created. I've written a macro for setting ON all items in pt except those ones in the array (listOffnet within the code). Everything works properly.
Now, I would like to do the complementary action: setting OFF all items that are not in the array. Unfortunately, I get the error at line:
[Code]....
View 1 Replies
View Related
Jul 31, 2014
The macro I'm writing (Excel 2010) is a loop that inserts a column based on a variable location, enters in a formula in the first cell, enters another formula in cell 2, then should fill that second formula to the last row, then keep going until the loop ends. There is always a chance that there will be blank cells to the left and right so I didn't use xldown. I've tried writing in multiple ways, but I keep getting the error.
Code:
Sub colfrmadd()
Range("A1").CurrentRegion.Select
colcnt2 = Selection.Columns.Count
[Code]....
View 1 Replies
View Related
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
Feb 24, 2010
I am getting the following error when i am clicking a Button on sheet1 which is generating duplicate of sheet1.I am trying to delete the button when duplicate is created..Error is:" run-time error '1004' Unable to get the buttons property of the worksheet class" at this line in code"ActiveSheet.Buttons("CmdHistory").Delete"
View 2 Replies
View Related
Mar 19, 2014
I locked the cells on Sheet1 from B4 to B10.
I protect the Sheet1 Review > protect sheet and selected two checkboxes from protect sheet property "Select locked Cell" and "Select unlocked cells".
It is working fine and it does not allow the user to view the locked cells B17:B24 while protection is on.
I have a RadioButton1 on the same Sheet1 and i put the following code behind the button to hide the 4 up to 10 rows. When i will press the button while password protection is on; it is giving me the following error:
Unable to set the Hidden property of the Range class.
View 7 Replies
View Related
Jul 30, 2013
Here is my code:
Code:
Private Sub ComboBox1_Change()
ActiveSheet.Rows("3:62").Hidden = False
Range("D1").Value = ComboBox1.Text
End Sub
I get a runtime error '1004' when I make my combo box selection. I don't have any security or password protection type stuff. The combo box is part of a more complicated spreadsheet and I don't want to switch to a data validation type drop down and I really want it to be activeX.
View 4 Replies
View Related
Oct 31, 2006
I have recently tried to implement the password protect from viewing code from this site. It worked brilliant on a basic spreadsheet, but when I tried to implement it on a worksheet with links I keep getting the error:
Run-time error '1004'
Unable to set the hidden property of the Range class
View 9 Replies
View Related
Jun 20, 2013
1.) I have excel 2007 and when I recorded the macro yesterday it worked just fine, but today it's coming up with the box to update values. The macro is set to open up the vendor assignment sheet and do a vlookup against the clerk and then return the information to the original sheet and then copy paste special values. 2.) Today it's also doing the calculating thing in the corner using 2 processors which it has not done before. 3.) Run-time error '1004': PasteSpecial method of Range class failed.
[code]' Keyboard Shortcut: Ctrl+r
'
Dim OriginalSheet As Workbook
Set OriginalSheet = ActiveWorkbook
Columns("B:B").Cut
With Columns("A:A")
.Insert Shift:=xlToRight
End With
[code]....
View 2 Replies
View Related
May 3, 2013
If I type in the VBA Editor immediate window
Code:
?application.WorksheetFunction.average(array(1,3))
I get the expected answer of 2, but
Code:
?application.WorksheetFunction.mode(array(1,3))
produces a pop up error of:"Unable to get the Mode property of the WorksheetFunction Class"
(Excel 2010 on 64 bit machine)
View 2 Replies
View Related
May 1, 2012
I am using Excel 2010. Why I do struggle with setting the range below
Code:
Set rng = Sheets("Data").Range(Cells(4, firstcol), Cells(lastrow, lastcol))
I get run time error 1004
Tried simple code from msdn and it return same error
Code:
Range(Cells(2, 3), Cells(10, 4)).Select
View 2 Replies
View Related
Oct 30, 2009
I'm trying to unhide all the cells in a worksheet, but keep getting the "Unable to set the Hidden property of the Range class" error and I'm not sure what to do.
View 2 Replies
View Related
Mar 27, 2009
I am encountering this error when I try to run code I have written on my own machine. I am working with all unprotected sheets, and I am running Excel 2007. All of the sheet exist.
Here is my ....
View 10 Replies
View Related
Jul 31, 2009
Sub ResetMacro()
Range("G26").Select
ActiveCell.FormulaR1C1 = "=Template!RC"
ActiveWorkbook.Sheets("Template").Range("G26:AE86").Copy
ActiveWorkbook.Sheets("Portfolios").Paste
Selection.AutoFill Destination:=Range("G26:AE36"), Type:=xlFillDefault
Range("G26:AE36").Select
Selection.AutoFill Destination:=Range("G26:AE86"), Type:=xlFillDefault
Range("G26:AE86").Select
End Sub
RunTime Error 1004: AutoFill method of range class failed
Takes me to the code and highlights in yellow the line in bold.
View 9 Replies
View Related
Sep 26, 2013
My excel version is 2007 and i am on WIN7 64bit
I have a workbook with VBA which was working fine earlier. Lately i have been getting
"Runtime error 1004" Select method of button class failed.
I am adding buttons dynamically in a loop in a sheet and what surprise me is , i go to debug mode when error pops up and it points to below line
MySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
but my code is running in a loop and i can see that atleast 20 buttons were added and it fails to select when it try to add this iteration. It is completely confusing me.
My entire below code is in a loop
Code:
maWrk.Cells(xIt1, yIt2).Select
mySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
Selection.OnAction = "callMe"
[Code]....
I also notice that when it fails, on select method .. the button was actually added to the sheet with caption/name as "Button 65536" but then fails to select it. What makes the select to fail after adding the button? Could there be anything special with Button 65536?
Is there any better way to code the adding button and setting action and name for excel 2007?
note before entering the loop i am deleting all the shapes with myShape.Delete which name matches "btRun"
I also want to highlight that i don't have 65536 rows in my sheet. I just have 200 rows. and when this error happens it is on row 150 so firstly i wonder why it is naming the button as Button 65536. I would assume it will start with 1 and go on increments.
What makes excel to fail to select an added button in VBA?
View 6 Replies
View Related
May 17, 2014
I'm using Excel 2013 and I'm getting an issue in vba I can't figure out. (This is something I've done several dozen time before) But everytime I try to copy a sheet in a workbook,
Sheets("Sheet1").Copy After:=WB.Sheets(WB.Sheets.Count)
I recently copied in this sheet from another workbook, and deleted all of it's formula names, but I can't copy any other sheets now either.
The Run time Error 1004: Copy method of Worksheet Class failed pops up. What is weird is that I put in a msgbox and
MsgBox (WB.Sheets.Count)
returns a "1" though the sheet has about a dozen sheets within it. I've saved the workbook and even saved it as another name.
The sheet I imported has a sheet number of 77 while the previous last sheet was 23, could this be a cause?
View 2 Replies
View Related
Apr 26, 2006
I am receiving the error "unable to get the find property of the range class" in my code. I have attached my code and highlighted red where my error is occuring.
On Error Goto ProductionRptEngineListExport_Error
Dim objWrkTmp As Excel.Workbook
Dim ObjWrkshtActive As Excel.Worksheet
Dim objWrkshtTmp As Excel.Worksheet
Dim RngStart As Excel.Range
Dim CurrentRowNum As Long, RowCnt As Long
Dim GroupRowNum As Long, ColCnt As Long
Dim chtChart As Excel. chart
Dim intwrktmp As Integer, intwrktmpTot As Integer, intLastRow As Long
Dim sngwrk As Single
ctlStatus = "Opening Production Report..."
DoEvents........................................
View 2 Replies
View Related
Apr 14, 2007
Having problems with trying to get my vba code to access the SpecialCells property. Receiving the following error.... Unable to get the SpecialCells property of the Range class. The section of my code is below that is causing the error. Keeps stopping on the "Selection.SpecialCells(xlsCellTypeVisible).Select" line.
Sheets(" Book Query").Range("A6:I6").Select
Sheets("Book Query").Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlsCellTypeVisible).Select
Selection.Copy
Sheets("Inventories and Variances").Select
Sheets("Inventories and Variances").Range("A7").Select
View 2 Replies
View Related
Nov 15, 2013
Trying to be slick with my macro I have 2 sheets collecting data in a workbook. I am attempting to send as attachments only those 2 sheets. I am getting an error at the following space within the code. How do I get around this?
Code:
Sub Send_to_Me()
' Send_to_Me Macro
Dim objTemp As Object
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim FileFormat As Variant
[Code] ......
This is the line that I get with the message above. It is only 2 sheets. Excel 2010 Windows 2007
View 2 Replies
View Related
Sep 1, 2006
this is some real simple code but for some unkown reason it keeps failing on me..
Sheets("All Work").Select
Cells.Select
Selection.Copy
Sheets("Sheet3").Select
Cells.Select
ActiveSheet.Paste
Sheets("All Work").Select
The second Cells . select doesnt work for some reason when i attach it to a button on the work sheet and i get the message
"runtime error "1004"
select method of range class failed"
but when i run just the macro it all goes smooth.
View 6 Replies
View Related
Jul 18, 2007
I've noticed error 1004 is very broad and switches its name depending on what you're working with. But I have no idea what it means.
This is the line w/ probs:
Sheeti.Cells(y, 2).Value = WorksheetFunction.DGet(InventoryRange, 3, CriteriaRange)
I looked at protection and Macro security and checked my definitions (that work in other contexts).
Does DGET need to have criteria and database in the same worksheet? My project is working between two workbooks.
View 6 Replies
View Related
Mar 30, 2014
I am executing code in a workbook that has been working successfully previously.
Suddenly I get a run time 1004 error in any macro in the workbook that tries to execute a activeworkbook.save command?
View 1 Replies
View Related
Jun 26, 2009
I keep getting that error when I'm running my macro. When I debug, it points me to the bolded line in the code below. The larger macro I'm running this function in runs this function some 101 times without error before this happens.
The values of the parameters are as follows when it gives me the error:
Find_Exact("hchen", ws1, "B:B"). The first parameter is the only one that changes in the previously mentioned running of this function.
View 4 Replies
View Related
May 1, 2014
The problem is coming from the Else statement.
Code:
Sub LockByColor()
ActiveSheet.Unprotect "pc"
Dim cl As Range
For Each cl In Range("$BO$1:$BZ$45")
If cl.Interior.Color = 6299648 Then
cl.Locked = True
Else: cl.Locked = False
End If
Next cl
ActiveSheet.Protect "pc"
End Sub
View 5 Replies
View Related
Sep 22, 2008
Sheets("PRODUCTIVITY").Select
Range("A20").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.Locked = False (***debug screen is highlighting this line***)
.FormulaHidden = False
.ClearContents
End With
Range("A20").Select
ActiveCell.FormulaR1C1 = "PASTE NEW DATA HERE"
Range("A20").Select
This worksheet was built for use of many users so this sheet is PROTECTED.
The user is instructed to copy/paste information from our internal web into the selected areas in the worksheet.
When i UNPROTECT the worksheet the VBA works fine.
The data in the worksheet spans from columns A to P, and the length (# of records) is variable.
View 9 Replies
View Related
Apr 27, 2003
When I try and run my macro I keep getting an error message saying Unable to get the Find Property of the Range Class and it refers me to:
TeamRef = Worksheets("Info Reference").Range("A3:A120").Find(What:=strFind1, After:=Worksheets("Info Reference"), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Address
View 4 Replies
View Related
Jul 17, 2007
I am using the below code to unloock certain columns based upon a value in cell a1. The value is the previosu month end date. It was working, but now I am getting " run-time error 1004 Unable to set the locked property of the range class" Can anyone help me to fix the error.
Sub UnlockMe()
Dim cl As Range
ActiveSheet. Unprotect "password"
For Each cl In Range("$B$1:$M$1")
If cl = [a1] Then
cl.EntireColumn.Locked = False
cl.EntireColumn.Interior.ColorIndex = 6
Else
cl.EntireColumn.Locked = True
cl.EntireColumn.Interior.ColorIndex = xlNone
End If
Next cl
ActiveSheet.Protect "password"
End Sub
View 2 Replies
View Related
Jan 18, 2012
I'm creating a simple bit of code which needs to include an array formula - but the code below fails;
Selection.FormulaArray = _
"=IF($L3="","",IF(ISERROR(INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1)),0))),0,INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1))))))"
"Runtime error 1004: Unable to set the FormulaArray property of the range class"
What am I doing wrong? The formula is fine when typed in.
View 7 Replies
View Related
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
View Related
Jun 6, 2005
Very new to VBA and having trouble with a simple macro running in 2003 that copies and pasts to another worksheet. Code as follows:
Private Sub CopyResults_Click()
'Select the filtered data and copy it
Range("A23").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F19").Select
' Open the template and copy in the data
Workbooks.Open Filename:="C:Athens Verification DataTemplatesVerification Template.xls"
Workbooks("Verification Template.xls").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
Application.CutCopyMode = False
End Sub
When I run CopyResults, the sheet is opened and the data copied correctly, but I get a runtime error 1004 past method of worksheet class failed, with the line 'ActiveSheet.Paste' highlighted. Don't get this when I run the same macro in 2000.
View 14 Replies
View Related
