How to hide Excel window from a user
Is there a way to hide Excel windows from a user - to prevent acidental closing it. ...
excel-vba
Excel: Create a new sheet for each row
In an excel sheet, I have roughly 30 rows x 100 columns of data. Each row represents a different "client". For each client, I've create a summary sheet that is emailed to them and that also contains all the information from my main sheet My question is as follows, is there a way for excel to create a new sheet based on some template she...
Macro to Loop through a range of cells, inputting data into other cells (drop-down selections) and return the result of those calculations
I have a calculator built in Excel 2003 in which you select options from a drop-down menu, data is then gathered and calculated returning your total cost to produce that package of items. What I'd like is to cycle through all the combinations of this drop-down and automatically return the total unit cost to another cell. I have already...
How to avoid Excel localizing object names and breaking macros?
I have this line of code in my macro: ActiveSheet.ChartObjects("Chart 6").Activate When I open the file and run the macro on a non-English version of Excel, this code breaks. For example on the Japanese system I tested it on, I would need to change the previous line to: ActiveSheet.ChartObjects("グラフ 6").Activate (グラフ means Chart in...
How to reassign the address of a named range in Open Office VBA?
In Excel VBA, I redefine the address of a named range with: Ranges("MyRange").Cells(1).CurrentRegion.Name = "MyRange" in the Worksheet_Deactivate event. This way, after updating the spreadsheet, MyDataRange always references to the entire data. Ok with Excel, but when I tried to migrate this to Open Office VBA (OpenOffice.org 3.1.1 /...
VBA excel Overload another add-in function
Question! I have an add-in we created at work, say foo.xla, and it has a function foo_sub(arg1). Now, I want to improve the way that foo_sub(arg1) behaves but withour editting the original add-in code, since as soon as they update it, I must migrate my code from the old add-in to the newer. Is there a way I could write foo_sub(arg1) i...
How to remove the recent document history in Excel Ribbon using VBA
How to remove the recent document history in Excel Ribbon using VBA. I am using the code below, but it doesn't seems to work. Sub Button1_Click() For i = 1 To Application.RecentFiles.Count - 1 Application.RecentFiles(i).Delete Next i End Sub Thanks ... ...
Microsoft Excel – How to copy cells to a different worksheet on every nth row.
Microsoft Excel – How to copy cells to a different worksheet on every nth row. I have an interesting problem. It is probably simple, but I can’t figure it out. I have a list of cells (about 10 columns across and over thousand rows, soon to be expanding) A1-A10 and down These are my headings and main input cells (let’s call this Summary...
Excel VBA Programming with Arrays: To Pass them or Not To Pass them?
Question: I am wondering which is the optimal solution for dealing with Arrays in Excel 2003 VBA Background: I have a Macro in Excel 2003 that is over 5000 lines. I have built it over the last 2 years adding new features as new Procedures, which helps to segment the code and debug, change, or add to that feature. The downside is tha...
removing the nulls from an array and copying to new array
I am splitting a line of text into an array then attempting to put into a new array without the null spaces. For some reason it's not copying from the old array into the new one. BTW if someone has a more efficient way of doing this I am open to that as well. Dim x As Variant, i As Integer, m As String, rdate As String, k(0 To 50) As V...
Excel, 2 sheets, 2 columns, same value?
Hi, I have 2 sheets sheet1 and sheet2 in an excel 2007 file. In sheet2 I have a column that is managed by a form/macro(with a tree view control). When an element has been selected, the cell is filled with an "x", when it has been unselected, the cell is filled with "" (nothing). In sheet1 I want to create a column equal to the sheet2 ...
How can I write to the console from Excel vba code?
Is there any way to print a message to the console from an Excel vba macro? I have a small VB .NET console application that calls an excel macro, and I'd like the error messages from the macro code to print to the console. Debug.print doesn't help as it only prints to the immediate window. Any ideas? ...
Excel: How to get a locale-independent printer name?
There's a PrintOut method in Excel that prints stuff. It accepts a printer name as a parameter, and that printer name is not just a system printer name, but a combination of both system printer name and port to which the printer is connected. .PrintOut ActivePrinter:="MyPrinter" & " on " & "Ne00:" If you only provide a system name, Ex...
Excel VBA App stops spontaneously with message "Code execution has been halted"
Hi, From what I can see on the web, this is a fairly common complaint, but answers seem to be rarer. The problem is this: We have a number of Excel VBA apps which work perfectly on a number of users' machines. However on one machine they stop on certain lines of code. It is always the same lines, but those lines seem to have nothing in...
Searching two columns and returning value from third VBA
Hello, A colleague of mine has an excel spreadsheet made up of 3 columns and would like to make searching them easier. What he has is two cells off to one side that he enters a value from column one into and a value from column two into. What he would like to do is search the spreadsheet for instances where value one and two exist in ...
What are the benefits of using Classes in VBA?
Hi All! I am doing some VBA programming in excel 2007 and have one workbook where all the datasheets is to be copied from, into another sheet. The new sheet will have several header rows, and I would like to keep track of where they are situated so I don't have to find words in them constantly. Is the simplest thing to use classes and...
How to pass Argument to an Undo handler in VBA
Hello! My first attempt at creating an Excel VBA. The macros below essentially change the background on the interior of a cell-range. GenerateMarkerOnSheet sets interior to black. ResetMarkerOnSheet is meant to roll-back the above change on Undo. Is it possible to pass the previous interior as an argument to the Undo hander (ResetMa...
Splitting a string with variable number of spaces VBA
I have a file with a bunch of number in columns. These numbers are separated by variable number of spaces. I want to skip the first line and get all the other lines and separte each number on the line. Finally, I want to write each number on Excel. I've been able to get the lines and write them on Excel but I can't separate each number (...
Excel VBA: Copy rows from another workbook, but just the function results
I want to copy 10 rows from a workbook into a new workbook. The copy & paste is fine, but in the copied cells are some formulas that I need to replace with the results. Is there a magical way to copy only the displayed values from the cells? This is how I do it now: Rows("2:11").Select Selection.Copy myWorkbook.Sheets(1).Activate Acti...
Excel VBA: Help with deleting cells based on conditions
I have a new job (yay!) which includes a chunk of Excel work that I have to do. I am new to Excel programming, so I am planning on picking up a book or two to start learning. But, in the meantime, I already have tasks to do, so I was hoping that someone would be able to help. I need help programming a little task with VBA. I have a list...