Unable To Set The Visible Property Of The Worksheet Class
I've been using the following code successfully for years. Today I would get the error 1004:Unable to set the visible property of the worksheet class. All my searches came up with someone not realizing they had protected the workbook. As you can see, the first thing I do before trying to set the worksheet visible is to unprotect the workbook. In frustration and on a lark I tried .Sheets("items").Visible = True and it worked. These kind of intermittent errors in Excel VBA are very frustrating. Can anyone tell me what I am doing wrong?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Unable To Set The Xvalues Property Of The Series Class
For the last day I have been getting the following error: "Run-time error '1004': Unable to set the Xvalues property of the series class." The archetecture of the marco is sound being that I have used this methology in another macro and it works correctly every time. The XValuesand Values range are also correct and I know this because I had the macro select the data cells and it does this correctly as well. The data for simplicity at the moment has no empty cells or abnormal data. The data cells are all filled with some kind of data. For the XValue Range the data would look something like "2070087 4/27/2007" and the Values range is filled with a number between 0 and 100. What I can't figure out is why it crashes with a valid establish range. I highlighted the lines it crashes on in read, they are near the bottom. Sub Proto1() ' ' Proto1 Macro ' Macro recorded 8/30/2007 by aaron.verellen ' ' Dim CurrentSheet As String, ChartName As String Dim RowCount As Integer, ColumnCount As Integer, FirstColumn As Integer, LastColumn, _ FirstRow As Integer, LastRow As Integer, Row As Integer, Column As Integer, _ ProductColumn As Integer, ArrayIndex As Integer, ParameterCount As Integer, _ ItemCount As Integer, MyIndex As Integer Dim UserProductFlag As Boolean, NewProductFlag As Boolean Dim Possibilities() As String Dim ProductXRange As Range, ProductYRange As Range .................
View Replies!
View Related
Unable To Set The Formula Property Of The Series Class
I am struggling with one problem. I am trying to change formulae of series in a chart using VBA code given below. But I am getting 'Runtime Error 1004, Unable to set the formula property of the series class' ActiveChart.SeriesCollection(1).Formula = _ Replace(ActiveChart.SeriesCollection(1).Formula, "Sheet1", "test")
View Replies!
View Related
Unable To Set The Locked Property Of The Range Class
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 Replies!
View Related
Unable To Set ColorIndex Property Of Interior Class: Fill Cell Color Macro
I have an error message that says: Run time error '1004': Unable to set the colorIndex property of the interior class. I attached code for your reference. If (Range("B10").Value = "Gift" Or Range("B10").Value = "Entertainment") And Range("C10").Value = "" Then Range("C10").Interior.ColorIndex = 6 MsgBox "Please Fill in the Person's Name & Company." Range("C10").Select Range("C10").Interior.ColorIndex = 6 End If
View Replies!
View Related
Unable To Get The Find Property Of The Worksheet Function Class
I am using the "Find" in VBA and wanted to test if the value searched for could not be found. So I get the error = "Unable to get the find property of the worksheet function class" When the text cannot be found. Dim zz As Variant zz = Application.WorksheetFunction.Find("xx", "Hello", 1) I also tried Dim xx as boolean xx = Application.WorksheetFunction.IsError(Application.WorksheetFunction.Find("xx", "Hello", 10)) But this produced the same error.
View Replies!
View Related
Unable To Get The Sum Property Of The Worksheet Function Class
Here is the code that generates the error: Set rng = Range("I:I") LR = ActiveSheet.Cells(Rows.count, "I").End(xlUp).Row + 2 Range("I" & LR).Value = Application.WorksheetFunction.Sum(rng) Range("I" & LR).NumberFormat = "#,##0" and from what I've read on google the .SUM(rng) need to be set to a range, but isn't it already a range? I tried plugging in there Range("I:I") for rng and that didn't work either.
View Replies!
View Related
Pivot Chart Error: Unable To Set The _Default Property Of The Pivot Item Class
I have created quite a nice little macro that; drills through a lot of key figures, updates a pivot chart and copies the chart to powerpoint. However it crashes on one particular data set every time with this error: Run-time error '1004': Unable to set the _Default property of the pivot item class The code where the debugger stops is the last line below here. lngKpi = Sheets("Helpfile"). Cells(lngRow, 2) Sheets("Charts").Select ActiveSheet.ChartObjects("DK").Activate ActiveChart.PivotLayout.PivotTable.PivotFields("KPI # (overall").CurrentPage = lngKpi I can manually change the pivot chart to the keyfigure it crashes on and thereby workaround the problem in the macro. Also it works for more than 50 other keyfigures without problems.
View Replies!
View Related
Unable To Get The VLookup Property Of The WorksheetFunction Class...
I am trying to look at column at of two different workBOOKS and then when a match is found take the value from column D in workbook two and copy it to the corresponding row in the current column of workbook one. this is the code I'm using and I get that unable to get the vlookup function. I thought maybe it was because not all of them will have matches so it could return an error if it is trying to return empty, so I put int on error resume next, and it ran through but never brought over ANY data, and there ARE matches and data to be brought over. destRng is the range in the current column(that i just inserted) on the active worksheet that we will be putting the new info into. srcRng is the range in column A on the other workbook that the numbers are being compared to. src is just an integer
View Replies!
View Related
Unable To The Locked Property Of The Range Class
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 Replies!
View Related
Unable To Get The Correl Property Of The Worksheetfunction Class
I get the "Unable to get the Correl Property of the worksheetfunction class" Error when I try to run the following code CurrentRow = 2 CurrentColumn = 2 Finalrow = 5 WS2 = Sheet2 Finalrow2 = 5 Num_Records = 4 Correlation1 = Application.WorksheetFunction.Correl(Range(Cells(CurrentRow, CurrentColumn) & ":" & Cells(Finalrow, CurrentColumn)), WS2.Range("B" & Finalrow2 - Num_Records + 1 & ":B" & Finalrow2
View Replies!
View Related
Unable To Get The FindNext Property Of The Range Class
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 Replies!
View Related
Unable To Get The Find Property Of The Range Class?
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 Replies!
View Related
Unable To Get The Vlookup Property Of The WorksheetFunction Class ..
I have a UserForm with one ComboBox and one TextBox. The ComboBox is populated from another sheet with product codes in Col A and names in Col B. When the user selects a product code from the ComboBox the respective name appears in the adjacent TextBox. Once both boxes have been filled the user can then save the data to a third sheet. Now the problem arises when the user tries to manually enter a code into the ComboBox that does not appear in its rowsource - VBA states a run-time error and my new keyboard goes flying out the window again. Attached is an example sheet
View Replies!
View Related
Specialcells Property Error "Unable To Get The SpecialCells Property Of The Range Class"
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 Replies!
View Related
Unable To Set Formula Property Of Series
I went through a lot of threads in this forum, although there were a couple relating to the same problem am facing, they didnt solve my error .. so here's my question again .. When am trying to delink charts , i get an error stating "Unable to set the formula property of series class". The code is as follows : Sub DelinkChartData() Dim iCtr As Integer, iChars As Integer, SH As Shape, iPlotOrder As Integer Dim nPts As Long, iPts As Long 'Holds the total no of points in the chart Dim xArray, yArray, sChtName As String, sSrsName As String Dim xVals, yVals Dim ChtSeries As Series ' var used to loop thru the series collection Dim ws As Worksheet Dim sChartType As String, iCtr1 As Integer
View Replies!
View Related
Unable To Set The Orientation Property Of The PivotField
I want the data field to change to a different field when a command button (cmd_view) is clicked. So when the command button has a caption of "Toggle to Feeds", the code changes the field from Sqm to Feeds, and changes the caprion on the command button to "Toggle to Sqm". When this is next clicked, the fields are swithed over, and the command button caption changes relative to it. The code I have written is below: The problem I have is when the code changes the view from feeds to Sqm, it works fine, but when the code changes from Sqm to feeds, it crashes out in the true part of the If statement on the code line ".orientation = xlhidden" with the error "Unable to set the Orientation property of the PivotField class". As the code for the change of view is the same, apart from the field names, I am at a loss on what is the cause. I have tried to record a macro to see if the field names appear differently, but they do not. Sub Tog_pivot() Dim Toggle As String Application.ScreenUpdating = False Toggle = Sheet5.cmd_View.Caption If Toggle = "Toggle to Feeds" Then 'switch view from Sqm to Feeds Range("F12").Select ' hide existing field With ActiveSheet.PivotTables("Pivot_forecast_old").PivotFields("Sum of Sqm") .Orientation = xlHidden End With ' Create & display new field "Feeds"......................
View Replies!
View Related
Receiving Error In Code "unable To Get The Find Property Of The Range Class"
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 Replies!
View Related
Unable To Get The Match Property Of The Worksheet Function
I'm trying to find the occurence of a date in a range. Here is the code I'm using: Windows(todaydate1).Activate For Each cell In range(Cells(top73, 3), Cells(bot73, 3)) z3 = DateSerial(Year(cell), Month(cell), Day(cell)) Windows("Cash Flow " & todaydate & " PM.xls").Activate Sheets("Commitments").Activate However the code stops right on the q3 line where the match function is located. The problem is that I'm getting a Run Time Error 1004: "Unable to get the match property of the worksheet function class" This would lead me to believe that the match function is not working. However, in Excel, it does work. Even when using the cell from the other Workbook, it works. All the dates which appear to be dates are indeed formatted as dates. The range is good. I've used the immediate window to verify the address and the values of the other variables in play. ?range(Cells(toprw, 2), Cells(botrw, 2)).Address $B$26:$B$49 Since the correct workbook and sheet is activated, I'm really at a loss here. Below is what the sheet looks like:
View Replies!
View Related
Sum Property Of The Worksheet Function Class
work around the sum error? Runtime Error '1004' unable to locate the sum property of the worksheetfunction class Public Function CalcSheet() Dim NoSales(0 To 11) As Range Dim DriveOffs(0 To 11) As Range Dim Voids(0 To 11) As Range Dim Shortages(0 To 11) As Range Dim tNoSales As Integer Dim tDriveOffs As Currency Dim tVoids As Currency Dim tShortages As Currency Dim X As Integer Dim NSc As Integer Dim DOc As Integer Dim VOc As Integer Dim SHc As Integer Dim aNoSales As Integer Dim aDriveOffs As Currency Dim aVoids As Currency Dim aShortages As Currency
View Replies!
View Related
Error 1004 Dget Property From Worksheet Function Class
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 Replies!
View Related
Class With Property As Collection Of Another Class
I've created a class called CStock. It has 5 standard properties (with Get/Let methods) as explained in that article above. Nothing fancy here. I also want to create a CPortfolio class that has 3 properties: pName, pNumberofPositions, and pHoldings. the first 2 are stardard, but the last one is different. I want it to somehow keep a collection of stocks.
View Replies!
View Related
Text Field Visible Property On User Form
I have a text field at the bottom of a user form that remains hidden (i.e. visible = false) until the user clicks the "Ok" button. At that point, I want the text field to appear as the macro is running (it's a large macro, so the text field just says "processing, please wait..."). The first line of my macro is:
View Replies!
View Related
Change Visible Property Of Check Box Based On The Value Of A Cell.
I have attached sample workbook that has a user form with 6 check boxes and 3 text boxes. The value of each of the text boxes is based on a cell value in Sheet2. I have the visible property of CheckBox5, CheckBox6 and TextBox3 all set to False. What I would like to be able to do each time the user form is opened is have the visible properties of those controls dynamically changed to True only if Sheet2 cell A3 has text entered in it.
View Replies!
View Related
Cannot Set Listindex Property!
For some reason my selection won't return to where it's supposed to in a listbox after I've increased it's column widths. It's very strange. I've put together an example of the issue as the actual workbook is enormous, but the demonstration seems to work. I've tried exporting the Listindex resetting code to an external function, but it didn't help either. Can anyone understand this behaviour?
View Replies!
View Related
Set Control Visibility Based On Tag Property
I have a userform which uses the tag property of the frame to determine whether a frame is visible (and hence the controls that reside within the frame). A frame contains three combo boxes, and six text boxes. The tag property of the frame matches the number of frames that are visible on the form, so that if the user selects five frames, frames 1 through five become visible and for all other frames visible = false. A frame contains all the data for a single entry. There can be up to fifty frames/ entries that are visible on the form depending on the user selection. I want to use the visibility property to do two things: first the combo boxes are filled from an array after the user selects the number of frames (or entries). I only want to fill the comboboxes where the frame is visible. The second thing is that I have a function which uses the data from the text boxes and combo boxes as required arguments. Since these are required arguments, I will get a data mismatch error if I try to call it and the controls are empty. Therefore, I only want to call the function if the frame which houses the controls is visible. Public Sub Visible1(Entry As String) Dim ctrl As Control For Each ctrl In UserForm2.Controls If TypeName(ctrl) = "Frame" And ctrl.Tag <= Entry Then With ctrl .Visible = True End With Else If TypeName(ctrl) = "Frame" And ctrl.Tag > Entry Then With ctrl .Visible = False.............
View Replies!
View Related
Set Variable To Forms Control & Get Value Property
How do I reference a spin button from a variable that I set. e.g Dim objSpn As Object Dim y As Integer Set objSpn = ActiveSheet.Shapes("spnWCDate") y = objSpn.Value This doesn't work, but hope it explains what I want. It's so I dont have to write the whole object name each time I reference it. ADDED: I've just noticed that this doesn't work even i do reference the object with the full name. I copied this from a forms spin control that I was using, why can't i store the value of the spin button in a variable?
View Replies!
View Related
Set XValues For Series Class (Chart)
I'm getting an error when I try to set the XValues for a chart in my excel worksheet. The chart shows up fine and the new series adds fine though when I try to set the XValues the below error occurs 'Unable to set the Xvalues property of the Series Class' 1004. My Code is below. Can anyone see anything wrong with it? This could be a floor in my understanding of the chart object. I don't want to be selecting charts and working on an activechart because I'll have several in the one worksheet.
View Replies!
View Related
Set PivotItem Visible To True
I recorded the following in a macro. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 8/16/2006 by BPETTIS ' ' With ActiveSheet. PivotTables("PivotTable1").PivotFields("CT Comp") .PivotItems("0.9").Visible = True .PivotItems("1.3").Visible = True .PivotItems("1.4").Visible = True .PivotItems("1.5").Visible = True .PivotItems("1.6").Visible = True .PivotItems("1.7").Visible = True .PivotItems("1.8").Visible = True .PivotItems("1.9").Visible = True . It was recorded when in the indcated PivotField is selected the "ShowAll" option. However, when I try to run this macro I get the following error "Run-time error '1004': Unable to set the Visible property of the PivotItem class Why am I not able to execute a recorded macro. Also how do I set a Pivot Item's visiblity to "TRUE"
View Replies!
View Related
Userform Object Moves When Set To Visible
In design mode on my UserForm, I have an object on top of all other objects (it's hidden until a button is clicked). That's how I designed it and it was working fine when I ran the form. Now, when I run the form and click the button, the object appears at the bottom below all other objects. I've tried closing Excel & re-opening it, setting the ZOrder in design mode and in the CommandButton code--nothing works. If I did do something to cause this--I have no idea what it was. I've been moving and re-sizing some objects, but not adjusting the ZOrder.
View Replies!
View Related
Method Of Worksheet Class Failed
I have a worksheet "Create Origin Zones" - sheet #17 in the array - that has 56 checkboxes. Users can click anywhere from 1 to 56 checkboxes, and for each checkbox that is checked, I unhide a sheet "Shp Profile Tmpt", copy it after sheet #17, rename it to "Origin " + checkbox#, and give it a title based on a variable in another sheet. Once that loop is done, I then hide the "Create Origin Zones" sheet, but the user can click a button on the new sheet to go back to the 'Create Origin Zones" sheet and add more zones by clicking more checkboxes and re-running the macro. Problem is I get that "Run-time error '1004':Copy Method of Worksheet Class failed" when I copy too many sheets. All the solutions I saw involve saving, closing and reopening the workbook but this interrupts my loop. (It would save my workbook and close it.) I am trying to have somewhere in my loop, say every time 20 or more checkboxes are checked, its saves, closes, reopens and continues the loop to the next checkbox and repeats the copy and paste and renaming etc. Below is my Sub pick_origin() chkcounter = 0 shtnum = Sheets("Create Origin Zones").Index ' find sheet# of 'Create origin zones' to copy sheets after For i = 1 To 56 'for the 56 origin zones checkboxes
View Replies!
View Related
Put A Formula In The First Worksheet That Will Compare The Property Numbers
I have a worksheet with property numbers in colum A and first names in column B. Another worksheet has property numbers in A, first names in B, and last names in C (different order, all mixed up) I want to put a formula in the first worksheet that will compare the property numbers and when it finds a match put the last name from the second worksheet into the first. Does that make any sense? I have tried vlookup but i'm obviously doing something wrong. example below.... # first last 123 x 124 y 125 z another sheet looks similar but has info in the "last" column. the numbers between the two sheets are not in the same order so i need to search by number and put the info from the second sheet into the first.
View Replies!
View Related
Copy Method Of Worksheet Class Failed
I am getting this error when I run the following VBA script. Sub AutoShape3_Click() Sheets("Template").Select ActiveSheet.Copy After:=Sheets(14) Duplicate.Hide wksName.Show End Sub The break mode is highlighting ActiveSheet.Copy After:=Sheets(14) as the source of the error. I've run this code many times before with no problem. The workbook has 48 worksheets in it. It's my understanding that Excel can handle many more worksheets, so that shouldn't be a problem.
View Replies!
View Related
Select Method Of Worksheet Class Failed
I have all sheets selected: Dim ws As Worksheet For Each ws In Sheets If ws.Visible Then ws.Select (False) Next ' The thing is that i now want to ungroup or select the first worksheet Sheets(1).Select ? And then run the same sub on all the worksheets by this: Dim wSheet As Worksheet For Each wSheet In Worksheets Next wSheet Debug error is: Select method of worksheet class failed: Sheets(1).Select ?
View Replies!
View Related
Copy Method Of Worksheet Class Failed ..
I've developed an administrative package in Excel for an After School Care programme. Essentially, there are 57 Child Records. Each record is stored on its own sheet. There are four other sheets in the workbook: three hidden templates, and the front page which holds a summary of all child attendance and balances. Up until a short time ago, invoices were created from an "Invoices.xlt" file - the program would open the file as a new book, and copy the 1st Template page out until all invoices were created. This worked fine. I've recently changed this so the Invoice template is stored within the main workbook. After I did this, we started having problems. We can run off up to 25-35 invoices fine (32 at this stage), but eventually it hits a point where the Activesheet.Copy command fails. After this point it is impossible to copy further sheets. I CAN, however, use Insert -> Worksheet. It is only the copy function that fails. Because it's such a strange problem, I've uploaded an example with children's names changed. It can be found on [url] The steps to recreate the problem are simple: Open the file, select all of the children's names (from Child to Child z), and click the "Create Invoices" button at the top.
View Replies!
View Related
Paste Method Of Worksheet Class Failed - But Not On Step-thru Using F8
My spreadsheet has a grouped graphic that I need to repeat in a variable number of rows. I wrote a macro that copies the grouped graphic from above and pastes and positions it in the newly inserted row. It works great when I use F8 to step thru, but if I try to actually run the macro, I get an "Paste Method of worksheet class failed." error. Sub InsertEmployees() Range("B18").Select Selection.EntireRow.Copy Selection.EntireRow.Insert Shift:=xlDown ActiveSheet.Shapes("Group 129").Copy Application.CutCopyMode = False ActiveSheet.Paste End Sub
View Replies!
View Related
Lock A Worksheet Tab So It Is Always Visible
I need to lock a worksheet tab (Main menu) in place so that it is always visible. There are multiple tabs that are all linked to the main menu by hyperlink. When work is finished in a worksheet I want the main menu tab to be visible and usable rather than using the scroll functions to get back to the main menu.
View Replies!
View Related
Rows On Worksheet Are Not Visible Or Sequential
This is so simple that I cannot even do a search for the answer. My worksheet has a horizontal and vertical cross. After row 16 for example, what is seen on the screen is row 24 for example. I did look at the instructions for "hidden" but I don't think this is a "hidden" issue. I don't want the rows to "jump" numbers, but want them listed sequentially down the page.
View Replies!
View Related
Paste Method Of WorkSheet Class Failed. Error 1004
I am trying to filter and copy from masterlist to wholesalecertified. The data is actually pasted to WholesaleCertified, however, I got error 1004. Sub Refresh() With Application .ScreenUpdating = False .DisplayAlerts = False Sheets("WholesaleCertified").Select Cells.Select Selection.ClearContents Sheets("MasterList").Select................
View Replies!
View Related
|
TRACKER
