I am an experienced developer notably in C#. I need to help a non-programmer friend get thigns done with Excel VBA. What is a good book for me to quickly pick Excel Macros & VBA up so that I can help my friend? We're using Excel 2007. ...
How can I automatically execute an Excel macro each time a value in a particular cell changes? Right now, my working code is: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("H5")) Is Nothing Then Macro End Sub where "H5" is the particular cell being monitored and Macro is the name of the macro....
Hi, I have to make column charts in Excel using VBA only (no user input). I wanted to format the labels of the x-axis so that the alignment for every label becomes -270 degrees. (This can be done manually by changing the "Custom angle" property in the "Alignment" tab of the "Format Axis" Dialog.) I have tried recording a macro for this ...
Hi, I have to display a column chart in a user form in VBA. Exporting the chart to GIF and importing in a picture box works; but I cannot use it because the chart needs to be modified based on options chosen by the user from the same form. I have been searching and people have commented that default OLE/OCX controls provided by MS may b...
Is there a way to make a paragraph in a Microsoft Word 2003 document readonly using VBA? ...
How can I determine if a user, in say Access, is a member of an Active Directory Security Group? I'd rather not build a whole authentication system into my little Access DB. Thanks ...
I find that when I'm reviewing VBA code most of the bugs are dumb ones, typos, Not setting things to Nothing, not closing DB connections, etc. I know that using Option Explicit can solve some of these, but I'd rather something more powerful. So... Are there any static analysis tools for VBA? ...
I'm at a loss to explain this one: I'm getting an error "91" (Object or With block not set) on the second line below: Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("SELECT * FROM employees") The following also causes it: Set rs = CurrentDb.OpenRecordset("employees") Executing ?CurrentDb.Name alone in the immediate w...
Hi, I'm new to VBA and have been throwing together a small macro application for the Office. We've got about 80 users on essentially identical PC setups, and it will be accessed by all but a few users. I've been playing around with some automation of accessing web pages using the Web Services references, and I've also loaded the Micr...
Hi, I am working on a Word VBA macro app for 80 or so users. The office has high staff turnover, so training suffers, and so one of the self imposed requirements for this project is comprehensive, friendly documentation. However, to supplement this, and to save newbies having to open up a 100 page document when they want to try somethin...
For various reasons, I'm stuck in Access 97 and need to get only the path part of a full pathname. For example, the name c:\whatever dir\another dir\stuff.mdb should become c:\whatever dir\another dir\ This site has some suggestions on how to do it: http://www.ammara.com/access_image_faq/parse_path_filename.html But they seem rat...
Hi, I'm trying to write a VBA script that will draw buttons beside the PageFields in a Pivot Table, these buttons will loop through the values in the PageField. I had this working for a regular Pivot Table, but I've been asked to adapt it for an OLAP Cube (External Data Source) and I can't work out how to find the values for a Member usi...
HI, I am looking for some material giving introduction to DAO and ADO usage in VBA. Most of the material i came across give examples of how to use them. I am looking for a broader theory based definition of what they are and how they came about and the requirements for their usage. thanks regards tksy ...
I'm developing an Access 2003 Database that uses a MS SQLServer backend. I'm trying to do Form Validation and am experiencing some problems. ValidationRule for each field seems to be ignored I can't figure out what event I should override to enforce validation without having the database do it. (I'm not against this, it's just unknow...
Is there a method available for copying a section out of a shape to another shape using VBA? I'm specifically trying to copy all the custom properties and user cells from one pagesheet to another page. ...
I have a csv file of 1.2 million records of text. The alphanumeric fields are wrapped in quotation marks, the date/time or numeric fields are not. For example "Fred","Smith",01/07/1967,2,"7, The High Street","Anytown","Anycounty","LS1 7AA" What I want do is write some VBA in Excel (more or less the only tool available to me that I am ...
Can someone tell what is wrong with this query? sqltext = "SELECT utyp, count(*) AS anzahl INTO UTYP_Anzahl FROM 01_umwelt WHERE [01_umwelt].status = Me.Controls(""STATUS"").Value GROUP BY utyp;" I am getting run time error 3075. ...
I'm trying to write a custom function that will let me retrieve a cell from the first row in a range that meets x number of criteria. I imagine this would be very similar to the way SUMIFS works, just simpler in that it doesn't continue processing after the first match. Does anyone know code to reproduce the SUMIFS (excel 07) function ...
Hi all, just wondering if anyone could suggest why I might be getting an error? I'm currently trying to execute a macro in a workbook by calling the Application.Run method that the interop exposes. It's currently throwing the following COM Exception: {System.Runtime.InteropServices.COMException (0x800A03EC): Cannot run the macro Macro1...
Hi there, I'm having some trouble with a large spreadsheet of mine. I bring in a lot of raw data into a data sheet, and then do a number of lookups across the data. Using built in functions I've come up with =IF(ISNA(INDEX(Data!$L$7:$L$1100,MATCH(Data!$I$2&$B$199&$B29&Data!$J$5,Data!$K$7:$K$1100&Data!$J$7:$J$1100&Data!$I$7:$I$1100&Data!...