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".
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]....
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].....
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] ......
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].....
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 RelatedIs it possible to have a hidden sheet password protected, so that only I can unhide the sheet ?
View 1 Replies View RelatedI'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]....
I need this code below to work in a macro on a hidden sheet.. FYI the sheet name is "Closed".
View 2 Replies View RelatedI 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 RelatedBasically 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..
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.
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.
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
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 RelatedI 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)
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.
.............................................
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.
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] ..........
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............
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 RelatedImagine 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).
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] ......
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 RelatedI'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] ............
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 RelatedI 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 RelatedI 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.
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"
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