Please help me copy a string from a listbox when a user hits ctrl+c. I was using the dataobject but for some reason this worked perfectly some times and gave me an error message other times. If you know why this is, stop reading, as the rest of this question is not necessary. Now I am putting this in a worksheet cell and using range.cop...
Hi Guys and Girls, I have a SQL 05 Stored Procedure that brings back around 180 rows with the structure: ID | Name | ParentId. What I would like to do is create a Treeview based on the records returned. Below is a sample I adapted from elsewhere on the forum (here) I nearly have it doing what I want but not quite. This is the proble...
I am a bit confused about scope of named ranges in Excel. I have two versions of an excel workbook. Occasionally, the users need to copy data from an older version of the workbook into a newer version. There are some custom VBA functions used in most of the cell calculations. Each of those functions looks up around 4 to 12 named rang...
I have a excel cell where its value is updated every minute. What i need is a alert message box; while that cell value moves to a particular time. Suppose a cell has value 10 if it reaches 7 in one minute .. i need a message box to alert that. if it is not reaching 7 in one minute its not needed to be alerted. Please help to write...
I'm trying to get a handle to the worksheet that was created by a copy operation. following code used to work: Dim wsTempl As Worksheet, pageCount as Long Set wsTempl = Sheets("Template") For pageCount = 1 To 5 wsTempl.Copy After:=Sheets(Sheets.Count) Set ws = Sheets(Sheets.Count) ws.Name = "p" & pageCount Next But stopped w...
Hello, I'd like to initialize a ComboBox that exists on a Chart (a stand-alone chart, not a chart embedded in a sheet) to have two values, "Expenses" and "Income". I tried entering a literal list ('={"Expenses", "Income"}' or '=("Expenses", "Income")') but trying either of these gave me the error "Reference is not valid". (The place I...
I have an Excel 2007 workbook which I am using to connect to a MSSQL 2008 server to pull down some names, I am able to achieve this successfully. My problem is I would like new worksheets created from the names that it gets from the SQL Server. I have been able to create an array of the results and iterate through the array creating a ...
I have an array constant defined in cell A1 as {1,2,3}. This displays as "1" (the first value in the array). I would like to have the formula SUM(A1) return 6. However, SUM is using A1 as a single-celled array, rather than the array constant contained inside A1 - and therefore SUM(A1) returns 1. Likewise, I would expect AVERAGE(A1)...
ActiveX combobox objects in Excel do not behave well when their ListFillRange refers to a formula-based Named Range (Defined Name). I think I have encountered other errors and possibly even Excel crashes thanks to this, but right now all that happens is the combobox_change() event is triggered anytime ANY cell in the workbook is changed...
Suppose, for instance, that I want a method that adds a ComboBox. Maybe I try this Public Sub AddComboBox() Dim cb As MSForms.ComboBox Set cb = <Calling form module>.Controls.Add("Forms.ComboBox.1") End Sub How can I get <Calling form module>? ...
I'm trying to open a series of Excel spreadsheets using an instance of Excel created inside of a module in an Access database. I can get the files to open properly; however, the actual call to make Excel start takes quite a while, and to open the files takes even longer. The location of the files doesn't matter (same time to open on a ...
I need to combine rows from two Excel worksheets into one long row, based on an ID column in each of the worksheets. Effectively I want to perform the equivilent of a SQL JOIN on two worksheets to produce a combined worksheet. I can get a Range object for a row in the first worksheet, and then use the ID column to get a Range object for...
I have written a VBA macro to do some stuff in Excel, and I want to know if the following is possible. How do I protect a Macro so that no matter who uses the spreadsheet, they cannot edit the code. (IF they press ALT F11 and pull up the macro Edit button options need to be greyed out, even if the macro has been selected).? Also is it ...
Hi, Possibly, my questions do not presume a simple answer as I'm asking to share a specific experience. What kind of issues you experienced trying to execute Excel macros through COM Automation? Are there any specific requirements for creation of macros to be run through Automation? Can you share your experience with data (scalar and...
I'm using the Dictionary class in the MS Runtime Scripting library to store where labels are going to go for a report template. Is there a way to iterate over all the key value pairs in that dictionary like in Python? I just want to use the key as the row number (It's all going in column A) and the value will be the label header. Some...
I have an Excel 2007 Sheet with Macros. When I click on a button, it immediately pops up an error "File Not Found: VBA6.DLL" Problem is, I have been using this sheet forever, and never seen this problem before. I did make some very very minor changes to the Macro code. And, I have 2 other sheets with identical macro code which I made t...
I am writing a reporting tool to document Excel files for various "compliance criteria", including wkb.VBProject.Protection to report if the VBA is locked. But how can I find if the workbook HAS any project ? If I calculate wkb.VBProject.VBComponents.Count - wkb.Worksheets.Count - 1 '(for the workbook) that will give me the number ...
After creating a new workbook, I am trying to solve a newly created worksheet of this workbook in VBA. Despite of activating the new worksheet, Solver attempts to solve the worksheet, where the macro lies. Any suggestions, on how to make sure Solver solves the right worksheet? I use this code to activate the worksheet. ws.Activate ...
Hello all I am getting an error Run-time error '9': Subscript out of range with the following code Public newarray() As String Sub test1() Dim int1 As Integer int1 = 0 Do int1 = int1 + 1 newarray(int1) = int1 * 5 Loop Until int1 > 3 End Sub when i decare the array Public newarray(4) As s...
All, I'm trying to use vlookup in a simple VBA function, but it is continually returning #VALUE! Here is the code: Public Function getAreaName(UBR As Integer) As String Dim result As String Dim sheet As Worksheet Set sheet = ActiveWorkbook.Sheets("UBR Report") ' check level 3 then 2 then 4 then 5 result = Application.Worksh...