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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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 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 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
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
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
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
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
Combobox Returning Listindex, Not Value
I have a combobox which properly populates the list of items it should display, however upon selecting the item the combobox displays that item's listindex, not the cell contents. I've tried to populate the combobox a couple different ways and the list from which to choose displays properly all the time, but upon selecting I cant get it to stop displaying the listindex.
View Replies!
View Related
Dynamic Combobox: Listindex Of A Given Box
I am working in excel 2003. I have a number of combo boxes, and I would like to get the listindex of a given box. It is easy to get the value of the box number "x" Controls("Combobox" & x).value works no problem, however Controls("combobox" & x).listindex does not work, and while I can do Combobox1.listindex I cannot figure out how to make the 1 a variable.
View Replies!
View Related
Listindex Max Rows Workaround
I have a dateset that I am creating a userform for. The problem is my dataset has more than 32765 rows, usually around 35000. How can I work around getting the "overflow" error when my users click on a selection > the limit imposed on a listbox?
View Replies!
View Related
Deleting Rows Based On ListIndex Match
I need some assistance with the code below. I have a userform that has a listbox with 2 columns of information. When there is a match between the value in the listbox and the value in the cell for a given row, that row is deleted. I thought this was very straightforward, but i'm not getting this to work right... Private Sub cmdDelete_Click() Dim Val2 As String Dim Val3 As String Dim doit As Long Dim roww As Long Dim LastRow As Long Val2 = ListBox1.List(ListBox1.ListIndex, 0) Val3 = ListBox1.List(ListBox1.ListIndex, 1) doit = MsgBox("Confirm that you want to delete the entry", vbOKCancel) If doit = vbOK Then
View Replies!
View Related
ComboBox Error "Could Not Get The List Property - Invalid Property Array Index" When Typing Out Of Range
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 Replies!
View Related
Listbox Returns Same Listindex For Duplicate Bound Column
I have a listbox on a form that refers to a dynamic range. The problem I have is that the listindex returned from selecting an item in the listbox returns the same number for an entry of the same value in the first bound column (in this case Col A, see below). I guess it's kind of acting like a primary key if. My Dynamic Range that shows up In the listbox. Col A Col B 0700 1500 '<-- returns Listindex = 0 0700 1800 '<-- returns Listindex = 0 0800 1630 '<-- returns Listindex = 1 0800 1830 '<-- returns Listindex = 1
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
LineStyle Property
Sub test() With Worksheets("BUDGET") .Range("E10").EntireRow.WrapText = True With .Range("B10").CurrentRegion With Intersect(Worksheets("BUDGET").Rows("10:" & Rows.Count), .Cells).Borders .LineStyle = xlSolid .Weight = 1 End With End With End With End Sub This code works fine on my Laptop (2007). On Desktop (2003), gives error: Unable to set the LineStyle property of the Borders class .LineStyle = xlSolid highlited I have tried this (on both computers) in a larger procedure(where it should be) and in a Module of it's own. Same results.
View Replies!
View Related
Usedrange Property
macros in excel XP. I written up a marco code to do series of calculations for me. I have one program that will use different matrix size (say 5 rows by 5 colums for one set of calcuations and open up another, but with 105 rows and 35 columns) Now, the way it works is the user input data in row 1 and column 1 and the marco does the following codes- Dim numrows As Integer Dim numcols As Integer numrows=worksheets("template").usedrange.rows.count numcols=worksheets("template").usedrange.columsn.count thisworkbook. names.add "matrix",,,,,,,,"=!r2c2:r" & numrows & "c" & numcols this code defines my range that will be used in the formulas further down the code. the problem is that I test ran the code using the matrix=b2:ai105 and I was using the code for a run with matrix=b2:q77 but the code is giving me the range from the previous macro run (ie. b2:ai105) and want it to use the matrix based on new data size (say b2:q77). I tried using activesheet.usedrange into the macro, but it does not work. Basically I need to "reset" usedrange property so it uses the correct range every time.
View Replies!
View Related
FoundFiles Property
i was searching in VBA help & found this example: a macro that steps through the list of files found during a search and displays the path for each file. With Application.FileSearch For i = 1 To .FoundFiles.Count MsgBox .FoundFiles(i) Next I End With how can i modify this macro so that it displays only the file name not the full path for example instead of displaying "C:Documents and SettingsDesktopMainstatment1.txt" i want it to display "statment1.txt"
View Replies!
View Related
Change A Property Value In The Combobox
I have several comboboxes on a userform that work fine as long as I use a whole number, but if I try to use a number with a decimal, I get a run time error 'invalid property'. what I'm doing wrong or if I have to change a property value in the combobox?
View Replies!
View Related
Custom Workbook Property
I`m building a macro for which I would like to save a parameter for the macro to read and set. I would like this parameter to be saved (permanently) in something like a custom workbook property, i.e. Tarabo (=Yes/No). How can I create/read/wright using vba code?
View Replies!
View Related
Enable Property Of Textbox
With Sheet4 ActiveSheet.Unprotect Key .Rows("13:252").EntireRow.Hidden = False If Me.OptionButton1.Value = True Then .Range("MyMrate").Range(Cells(Val(Me.TextBox1), 1), Cells(Val(Me.ComboBox1), 1)).Value = .OLEObjects("TextBox" & i).Object.Text .Range("MyMcash").Range(Cells(Val(Me.TextBox1), 1), Cells(Val(Me.ComboBox1), 1)).Value = (.OLEObjects("TextBox" & i).Object.Text * (Range("MyRSF") / 12)) ElseIf Me.OptionButton2.Value = True Then End If End With For some reason, once the code runs, it calls this code, Private Sub combobox1_Change() Me.TextBox2.Enabled = Me.ComboBox1 "" If Me.ComboBox1 = "" Then Me.TextBox2.BackColor = RGB(225, 225, 225) Else Me.TextBox2.BackColor = RGB(255, 255, 255) End If End Sub And then I get an error message that the code could not set the enable property of Textbox2.
View Replies!
View Related
Property Of Tabbed Panel
I'd like to know if anybody of you knows which is the property that determines the number of tabs in a tabbed panel. I'd like to realize a dynamic tabbed panle where the number of tabbed panels are regulated by a variable.
View Replies!
View Related
ColumnHeads Listbox Property
Does anyone know how to get the colum headings in a list box to read something other than the column name? I want the column headings to say, "Printed", "Company", and "Part Number" instead of "Column O", "Column P", and "Column Q".
View Replies!
View Related
Invalid Property Value - Lock-up
I used to get this error when I accidentally tried to name two controls with the same name. This time I had just highlighted a set of labels to increase the width from 96 to 108. It locked up with that Invalid property value error. I just finished a revamp of my sheet (added 6 Columns and a UserForm) and was going to save it after this re-size was finished. OPPS! Is there any way to salvage this update?
View Replies!
View Related
Invalid Use Of Property When Calling Subroutine
I've ran into a problem with one of my workbooks which appears to only affect Excel 2007. It works fine in Excel 2003. Basically I am calling a sub routine contained in a module from worksheet code. The sub is Public in the module and I use the following code to call the sub.. The code Errors immediately here - Private Sub mybutton_Click() Worksheet Code - Private Sub mybutton_Click() mysub End Sub Module Code - Public Sub mysub() ...my code End Sub This works fine in Excel 2003?! Is there a different way to call the sub with Excel 2007
View Replies!
View Related
ComboBox Error :: Invalid Property Value
I am trying to get a combo box to work, but keep encountering the error "Invalid Property Value." When the combobox entry is deleted and the user moves to the next text box in the userform, this error pops up which is very annoying. It also pops up when the word entered doesn't match, like it is supposed to. I have MatchRequired set to True, because I want an error message to come up, but with my own error message like " That name doesn't exist, please try again ". I can't figure out a way to ignore the "Invalid Property Value." error message, and show my own customized message.
View Replies!
View Related
Property Management Availability Calculator
I am working on a Property Management spreadsheet to track the availability of vacant units (and upcoming vacant units). I'm trying to write a formula to count the number of current units that are listed as vacant, so that I can ultimately report on the overall unit availability as of any given period (today, 15, 30, 60, 90-days out). All of the data in this spreadsheet is dynamic and each field can change at any time. There are also a lot of blank fields as well. I am using Excel 2003. Here are the fields/data that I am using for my analysis: Column A - (Reporting Period Dates): A2 = 5/20/08, A3 = 6/5/08, A4 = 6/20/08, etc. Column B - (Unit Status: V=Vacant, O=Occupied): B2 = V, B3 = V, B4 = V, B5 = V, B6 = V, B7 = V, B8 = O, B9 = O, B10 = O, B11 = O, B12 = O, B13 = O Column C - (M/O Date. These dates represent when the current resident will move out) - Cells C2:C13 contain dates for when each tenant will move out Column D - (M/I Date. These dates represent when the future resident will move in) - Cells D2:D13 contain dates when the future tenant will move in If my reporting period is 5/20/08 (cell A2), then I need to calculate how many Vacant units (B2:B13) I will have as of 5/20. The formula will need to count all of the current Vacant units (B2:B13), plus it will also have to take into account any Move Outs (C2:C13) during the reporting period. For example, let's say we have an Occupied unit that is scheduled to move out on 5/15/08. The cell will show this as "O" but the formula will need to determine if the M/O date is <= the Reporting Date of 5/20/08 (A2). If so, then Excel would count this unit as a Vacant for the period. In addition to this, I also need the formula to look at upcoming Move IN dates (D2:D13). The formula will also have to determine if the MI date is <= the Reporting date (A2). If the MI date falls prior to the Reporting Date, then this unit should be considered as Occupied and should NOT be added to the total available units. NOTE - there are quite a few cells in column D that are blank and don't have MI dates. If there isn't a MI date, then the formula should assume that those units are Vacant if it is past the prior tenants MO date. I'm assuming that Excel can handle multiple conditions like this, however, I am not smart enough to figure it out.
View Replies!
View Related
Page Setup And Zoom Property
I was curious if in VBA is there a way to switch in the page setup from Fit to X Page by X Page to the Scaling %. I have set all my pages to fit 1 x 1 and would now like to know the scaling % (Zoom) of the sheets.
View Replies!
View Related
Combobox Match Entry Property
I have a ComboBox (cmbJobNo) in excel which lists a series of numbers. By using the code below other TextBoxes are automatically filled with related text once the ComboBox number is selected. On typing a number in the ComboBox the number is predicted, which is great except when a number is not sequential. Say the number is 15304 (the next number in list is 15315), when 1530 is entered, 15304 is predicted and VBA jumps to the next data entry Textbox, the data entry person then enters 4 (they type a lot faster than me, without looking), which is incorrect for that box. VBA does not allow the complete number to be entered. If I turn off MatchEntry the other related textboxes txtClient and txtProject) do not automatically update when number is entered. What can I do to allow complete number to be entered and related Textboxes updated after number entry? Private Sub cmbJobNo_Change() If cmbJobNo.ListIndex > -1 Then txtClient = Format( Range("Jobs").Cells(cmbJobNo.ListIndex + 1, 2), "0") ...............
View Replies!
View Related
Cannott Assign To Read Only Property
A have a problem with on of my modules. I downloaded a module for create a counter for my application in Excel. I integrated them successfully. But after i wanted rewrute them for my own purpose and insert in them my own instance of class..But them i want to let a property of my odject them compilator return me an error ..This is a module
View Replies!
View Related
Assign Currency To Textbox Property
how I might assign the $ Currency property to one of my textboxes in my user form. I was hoping I could set the property in the spreadsheet and the form would inherit what properties the cell in the spreadsheet had but that did not work. Here is the line of code I tried to use to assign the property to the cell (TextBox 16 is the one I am trying to change) Set c = Worksheets("database").Range("a65536").End(xlUp).Offset(1, 0) Application.ScreenUpdating = False ' speed up, hide task 'write userform entries to database c.Value = Me.TextBox1.Value c.Offset(0, 1).Value = Me.TextBox2.Value c.Offset(0, 2).Value = Me.TextBox3.Value c.Offset(0, 3).Value = Me.TextBox4.Value c.Offset(0, 4).Value = Me.TextBox5.Value c.Offset(0, 5).Value = Me.TextBox6.Value c.Offset(0, 6).Value = Me.TextBox7.Value c.Offset(0, 7).Value = Me.TextBox8.Value c.Offset(0, 8).Value = Me.TextBox9.Value
View Replies!
View Related
Legend - Position Property In 2007
I have a 2003 vs. 2007 problem that I've spent a week or so trying to resolve, to no avail. I have created a fairly complex "dictator application" in Excel 2003. The application performs a bunch of calculations, and stores this inforamtion in a template workbook. The template itself contains one worksheet (to hold the data), and twelve empty "chart-sheets" which I later use to create plots. Of course, everything works fine and life is good in Excel 2003. Flash forward two years, and half of my company is running 2007. When a 2007 user fires the plot creation routine, he is greated with a lovely error message (# 2147467259): "Method 'Position' of object 'Legend' failed". Here's the full procedure I'm running to create the plots and position the legends: Function bChartSumFileData(ByVal lTotalPlots As Long) Const sSOURCE As String = "bChartSumFileData" 'Procedure Name (error-handling) Dim bReturn As Boolean Dim i As Long, j As Long, lNumSeries As Long Dim sTemp As String, sPlotSetting As String Dim chtTemp As Chart Dim wksData As Worksheet, wksInfo As Worksheet Dim serTemp As Series Dim uSettings As CHART_SETTINGS Dim vaPlotInfo As Variant Obviously, lots of custom functions here; I'm also using a custom "type" to store chart settings (which I read from a *.txt file in a separate function). However, as far as the Legend goes, it shouldn't make a difference with the above code. When I stop execution after the error and look at the template workbook, the chart is created just fine, and the legend is visible, so I'm not sure why the error is occuring. Even better, the number of charts created prior to erroring-out is not fixed: sometimes the error occurs on the first plot, sometimes on the third, etc.
View Replies!
View Related
Save File With Hidden Property
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 Replies!
View Related
Exit Sub Property Is Not Working Properly!!
I have the following two codes on a userform, The first code performs two steps: The first step is running the second code (MyDate), which is used for checking the entries in the userform textboxes and if there is any wrong entry a message box appears then it exits sub. However, what happens when it finds any wrong entry is that it displays the msgbox and instead of exiting sub as it is requested in the (MyDate) code, it resumes running and moves to the second step and adds 1 to ComboBox1 ListIndex.
View Replies!
View Related
Return A1 Reference From Series Property
A common task for me is having to manually update the series formulas on charts to conform to some predefined conditions as the data changes. To reduce the strain of this, I've developed a VBA module that will read the various series A1-style formulas of the chart and update them according the the specific conditions. This works wonders on most charts, but I've suddenly run into a situation where Series.Formula property returns ONLY the R1C1 style. That is, where my series formula looks like this in the chart sheet formula bar: ...
View Replies!
View Related
|
TRACKER
