Record Macro Window.

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Programming
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - worksheet menu bar Running 2003, xp pro sp 2. User cannot access the Options item on the Tools submenu in the Worksheet Menu Bar. At least one spreadsheet is open. Options is not greyed out. When anyone else logs onto her machine, it works perfectly. I logged on as Administrator and ran Repair via add/change programs - didn't help her. The registry contains the following in HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\Toolbars\Settings Value 7 Name: Microsoft Excel AWDropdownHidden Type: REG_DWORD Data: 0x1 Anyone know what this is? Should I remove it? She swears she has not deleted or changed anything and that it worked fine yesterday. Any ideas about what may have happened? Thanks - Mike Tag: Record Macro Window. Tag: 981337
  - 2
    - Any sample to connect a remoted database through Excel? Does anyone have any sample to connect a remoted database through Excel? I need to make a connection with a database in China, and I locate in HK. Could anyone give me any suggestions on what I need to retrieve any data from a database? What I can think of is URL, username, password, ... Does anyone have any suggestions? Thanks in advance for any suggestions Eric Tag: Record Macro Window. Tag: 981323
  - 3
    - VBA formula too long? Hi, I am creating a utilisation tool in Excel which contains data on Sickness, Holidays and Other Absence. I have created the formula below in Excel to account for how much Other Absence has been taken year to date. I have then transferred this into VBA and changed the format to R1C1 and adapted the ranges so that they are defined by two variables (j - start of year, based on what today's date is & k - end of year, based on what today's date is). See code below. In reformatting however the formula is pushed onto a new line of code and the usual "_" does not seem to read as me wanting the code to continue on the next line. Is it possible to have a formula that is simply too long? I am getting more confident in Excel and VBA now and can pretty much do everything I want to, but I am equally confident that there is a better way of doing everything that I do :) I am not especially fond of the formula / code below so please feel free to scratch it if that provides a more simple solution. (I haven't finished reformatting after I encountered the problem.) EXCEL FORMULA =(SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-((SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Education")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Child")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Doctor")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Dentist")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Emergency")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Maternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Paternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Flexi")))) REFORMATTED VBA CODE ActiveCell.Offset(-11, 0).FormulaR1C1 = "=(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C<>"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-((SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C<>"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C=""Education"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C=""Child"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1_ <B1)*(I26:I391=""Doctor"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Dentist"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Emergency"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Maternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Paternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Flexi""))))" Thanks in advance, JohnP Tag: Record Macro Window. Tag: 981321
  - 4
    - Getting folder size information into cell??? Hello, I can't seem to figure out how to get folder size information into an excel spreadsheet cell. I have the filer/server location information in one cell and I'd like to use the information within that cell to extract the folder/directory size and populate another cell. I can't seem to figure out how to go about this? Any suggestions and/or solutions (if any) would be greatly appreciated. Please bear in mind, I'm a noob with this excel programming. ;-) Thank you. Tag: Record Macro Window. Tag: 981320
  - 5
    - Merge muliple sheets in multiple workbook below each other. how do i Merge muliple sheets in multiple workbook (in same folder) below each other. However each respective sheet should go to a new sheet. Note all sheets in individual workbook are named same. Please help me. all responses appreciated. Thanks Tag: Record Macro Window. Tag: 981319
  - 6
    - workbook / worksheet change event Is it possible to use the act of navigating to a different worksheet as an event in VBA programming? For instance, if I had a workbook with multiple worksheets, and the primary sheet that is displayed when the workbook is opened is called "setup", I want to make sure that you cannot navigate to or view any other worksheets until all necessary changes are made to the setup worksheet. Tag: Record Macro Window. Tag: 981298
  - 7
    - Pulling data from 3000 files Hi All, I'm going crazy try to copy and paste data from 3000 files into 1 file. Is there a way to automatically open a file, copy columns 1 -5 and then append it to the file where I want it all placed? Thanks in advance... Tag: Record Macro Window. Tag: 981297
  - 8
    - My code enters break mode but there are no breakpoints For some odd reason my code is acting like there are breakpoints in a handful of locations. I tried exporting the module, deleting the old excel file, creating a new excel file, importing the module, and running it. Same thing happened. Then I copied the code into Word, deleted the Excel file, re-created it, and pasted the code into a new module. Same problem. Any ideas??? I'm using Excel 2007. Tag: Record Macro Window. Tag: 981287
  - 9
    - Macro to show/hide rows I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! Tag: Record Macro Window. Tag: 981276
  - 10
    - Variable Input I have to use, what I thought was, a simple variable input, but it's turning out to be not so simple. Basically I loop through all sheets in a book, as long as the sheets <> "Sheet1". Then do some basic formatting and some simple math. Everything works fine except for this part: Chng = Range("F2") and this part: c.Offset(, 19).Value = "=(IF(RC[-20]="&Chng&",RC[-18],0)+IF(RC[-15]="&Chng&",RC[-14],0))*RC[-3]" I have names in column F of each sheet, which are all the same on each sheet, but different names are on different sheets. All I need to do is figure out the value in F2 on each sheet and use that in my IF function. I thought I could just assign the value in F2 to the variable Chng and then just use that variable in my IF function. Excel has other thoughts and it is telling me that I can't do that. Why? All code below: Sub Math() Dim lastRow As Long Dim c As Variant Dim sh As Worksheet Dim myA As Range Dim lastYRow As Long Dim sumRng As Range Dim Tot As Double Dim Chng As Variant For Each sh In Worksheets If LCase(sh.Name) <> LCase("Sheet1") Then sh.Activate With sh .Rows(1).Font.Bold = True Chng = Range("F2") lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For Each c In .Range("F2:F" & lastRow) If c.Value <> "" Then c.Offset(, 19).Value = "=(IF(RC[-20]="&Chng&",RC[-18],0)+IF(RC[-15]="&Chng&",RC[-14],0))*RC[-3]" End If Next c lastYRow = .Cells(.Rows.Count, "Y").End(xlUp).Row Set sumRng = .Range("Y2:Y" & lastYRow) Tot = Application.WorksheetFunction.Sum(sumRng) 'MsgBox Tot .Cells(lastRow + 1, "Y").Value = Tot '.Rows(2).Delete End With End If Next sh End Sub Any help is appreciated. Regards, Ryan-- -- RyGuy Tag: Record Macro Window. Tag: 981275
  - 11
    - Macro Error Help!! Please I need help in resolving this error "Sub or function not defined". I tried using Ron's code http://www.rondebruin.nl/copy3.htm and i get error. Please help me in identifying what am doing wrong. all responses appreciated. Sub RDB_Module1() ***** This get highlighted Dim myFiles As Variant Dim myCountOfFiles As Long myCountOfFiles = Get_File_Names( _ ****this also gets highlighted I believe these two function are not properly called or referenced to. HELP Thanks Yossy Tag: Record Macro Window. Tag: 981273
  - 12
    - Count function? If Col A contains a text variable and column B contains a numerical variable associated with the text variable, what is the simplest formula I can put into a single cell that will count the number of occurrences of the numerical variables in Col B associated with the text variable in A? Tag: Record Macro Window. Tag: 981272
  - 13
    - Appending Contents Of Multiple Spreadsheets Into One Hello. I would like to open up a new spreadsheet and append rows 2 - EOF from six other spreadsheets into that new one, be prompted for the name to save it with, then save and quit. Would someone be kind enough to offer some VBA code to do this with? Thank you, Sheldon Potolsky Tag: Record Macro Window. Tag: 981259
  - 14
    - What's the API call to kill the KB buffer? I don't want keystrokes typed in advance of my UserForm appearing to remain alive and execute. Anyone know the API call (which I assume this requires) to flush the keyboard buffer? Thanks much. *** Tag: Record Macro Window. Tag: 981247
  - 15
    - ListBox Displaying With Some Items Already Selected In 2007 I have an application that I have been using without problems since Office 2000. It has a list box, which is populated using the command: cyo_dialog.sel_box.RowSource = "product_list" It then goes on to ensure that all the items are deselected with the code: For a = 0 To (cyo_dialog.sel_box.ListCount - 1) cyo_dialog.sel_box.Selected(a) = False Next a cyo_dialog.sel_box.ListIndex = 0 Since I started using this on 2007, for some unknown reason two of the first three items come up selected (normally item 1 with either 2 or 3) , and are then not deselected by the code that follows. I have found 2007 to be very buggy and inconsistent in its running, but I can see no reason why this should now be happening. Thanks in advance. Tag: Record Macro Window. Tag: 981246
  - 16
    - Checking if a cell contains a string that is not case sensitive I am trying to count how many times a specific string "abc" forms part of a cell in a spreadsheet. For example abcgfd, abcdse, efgabc would be vaild and I would like to count these abdcef, bcadef, cbaeds would not be valid and I would not want to count these. Can anyone help point me in the right direction? Thanks. Tag: Record Macro Window. Tag: 981244
  - 17
    - TreeView's pos on UserForm changes by itself. Until I complete the excruciating process of finding out how to disable screen updating while pruning a TreeView (deleting & moving nodes), I hit on the idea of setting TreeView.Visible = False, then = True again when processing's done. When I do, the TV's re-shows in a DIFFERENT position on the UserForm than before. (It has shifted to what seems like the U.L.-most corner.) NOW what's going on? Thanks. *** Tag: Record Macro Window. Tag: 981243
  - 18
    - updating pivot table data via external xls file which is replaced hi, the problem with my code for updating the table is that it works on my computer but if i try it on another one the pivot table itself isn't updated but a new worksheet created with an empty pivot in it ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "U:\xyz\xyz_Shared\test\TestLines.xls!R4C1:R50000C38" The file from which the table should get it's data is called TestLines.xls Tag: Record Macro Window. Tag: 981240
  - 19
    - Numeric data Dear all Pleasae guide me to find out numeric data in cell where the cell contains both aplha & numeric value. Like i want to take out pincode from one single cell where in the location and pincode is mentioned in a single cell Eg ( Mumbai 400018 ) Serch Result shud b 400018 Thanxs in advance Tag: Record Macro Window. Tag: 981235
  - 20
    - Cell references and merged cells Is there an explanation somewhere of how to handle column references when there are merged cells in another row? For example: Private Sub TestOffset() 'Expect references to columns A, B, and C. 'Get references to columns AB(merged), C, and D, if there are merged cells in other rows Dim r As Long Dim rng As Excel.Range Range("A1").Activate Set rng = Range("A1") With rng Debug.Print .Address .Value = 1 .Offset(0, 1) = 2 .Offset(0, 2) = 3 For r = 0 To 12 Step 6 Debug.Print .Offset(r, 0).Address, .Offset(r, 2).Address, .Offset(r, 4).Address, .Offset(r, 6).Address Next r End With Range("A20").Activate Set rng = Range("A20") Range("A20:B20").Merge With rng Debug.Print .Address .Value = 11 .Offset(0, 1) = 12 .Offset(0, 2) = 13 For r = 0 To 12 Step 6 Debug.Print .Offset(r, 0).Address, .Offset(r, 2).Address, .Offset(r, 4).Address, .Offset(r, 6).Address Next r End With End Sub Tag: Record Macro Window. Tag: 981232
  - 21
    - Excel 2007 SP1 VBA Help I have created a WortArt object in Excel programatically using VBA and using the preset text effect style msoTextEffect3. Unfortunately, this creates text with the color red. Now I can right click on the word art and change the color of the text to whatever I want. But I cannot find a way to do this programmatically in VBA. Also, trying to use Macro recording is useless for this. It looks like macro recording is broken in Excel 2007. Any help grateful Wheeley Tag: Record Macro Window. Tag: 981230
  - 22
    - Help to speed up this macro I have the following macro that runsa a UDF to delete blank rows (every other row). The formula is inserted in cell E11 and copied down for for each line where Col B is not empty. It works just fine, however, there are only 100+ lines and it runs very slowly. Is there something Ican do to speed it up? Sub BudgetJobNo() ' Sheets("Budget").Select Call RowsJoinData(2) '<where column 2 (B) is blank for every row to delete Application.DisplayAlerts = False Application.ScreenUpdating = False JobNo = "=IF(ISERROR(LEFT(Budget!B11,FIND("" "",Budget!B11)-1))=FALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" "")" Range("E11").Value = JobNo 'And then copy them down to the last line. Range("e11:e11").Select Selection.Copy CR = 11 Do While Cells(CR, 2) <> "" Range(Cells(CR, 5), Cells(CR, 5)).Select ActiveSheet.Paste CR = CR + 1 Loop Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -- Jim Tag: Record Macro Window. Tag: 981229
  - 23
    - How do you collapse a TreeView? I cannot seem to find even basic properties in VBA's TreeView. There is Node.Collapse, which collapses the immediate children of a single node. Gee, Beaver, that's swell. How do you collapse the tree? At the risk of being obvious, I mean: every node. Thanks much. *** Tag: Record Macro Window. Tag: 981228
  - 24
    - WM_SETREDRAW doesn't work unless...what? RM Smissaert kindly replied to my recent Q of how to disable screen updating in a TreeView: Private Declare Function SendMessage Lib "user32" Alias _ "SendMessageA" (ByVal HWND As Long, ByVal wMsg _ As Long, ByVal wParam As Long, lParam As Any) As Long Private Const WM_SETREDRAW = &HB *** SendMessage TreeView1.HWND, WM_SETREDRAW, IIf(EnableTheBugger, 1, 0), 0 (The 1 and 0 in my IIf are reversed from his example, which I presume is correct, 'cause it seems to work for me.) But this call fails, UNLESS (I found empirically) I, like, make the call, then show a MsgBox (which interrupts processing and "seeds" the screen first...?) Sort of like having to turn off the pilot before turning off your stove. What additional API call must I make if any to make WM_SETREDRAW work? *** Better yet, how do you do this in a native VBA way? What I'm doing is cleaning & pruning the tree--deleting & moving hundreds or even thousands of nodes. Each operation of which has to wait for my ancient video card, which I inherited from Johannes Vermeer, to repaint (in slow-to-dry Old Master oils) my screen. Thanks much. *** Tag: Record Macro Window. Tag: 981227
  - 25
    - Confrimation of copy paste operation Hi, In my application (C#, Office 2007), i am attempting to copy a range of cells containing data to a range on another worksheet. Upon copying the range I then copy a chart into a specific area on the second worksheet. The chart is resized and set up to represent a datapoint per row of data so the chart. Occasionally (and i mean occasionally, i cannot replicate the problem 100% of the time) the chart does not get copied to the second worksheet. I do this a number of times for various different table ranges. Sometimes there will be a number of these charts copied to the same output worksheet and i have encountered this issue where by only some of the charts copy and others don't. I generally use the following code to do the copy paste _chObj = (ChartObject)_excelDataWS.ChartObjects("ChartName"); _chObj.Copy(); _excelPresentationWS.Paste(_presTopLeftRange), paramMissing); I have a couple of questions on this problem. 1) Is there a benefit to using the following copy command?: _chObj.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlPicture); 2) Is there any way to confirm that a copy/paste operation has succeeded and if not then to retry it? I'm really having no joy in diagnosing why this is happening. I get no exceptions thrown when the charts do not appear. Any help is greatfully recieved. Thanks in advance, G Tag: Record Macro Window. Tag: 981226
- Next
  - 1
    - Capture paste from Office Clipboard Hi, A bit of background first I have an event macro which I want to prevent from running if the user paste some info from the office clipboard into a cell as it triggers my events. The below code works if the data is copied from one section of the spreadsheet to another. Is there anyway to capture Ctrl + V using select case?. Select Case Application.CutCopyMode Case Is = False MsgBox "Not in Cut or Copy mode" Case Is = xlCopy MsgBox "In Copy mode" Case Is = xlCut MsgBox "In Cut mode" End Select FYI I also looked at the on key event but didn't want to add a new module to the workbook if I can avoid it Application.OnKey "^v", "Macro1" If I had to add a module I guess Chip site would be the best start?? http://www.cpearson.com/excel/vbe.aspx Any suggestions of tips would be appreciated Regards VBA Noob Tag: Record Macro Window. Tag: 981221
  - 2
    - Modeless Form help please I'm using a modeless form with a spreadsheet. The reason for making it modeless is that I want to be able to use the spreadsheet in the background and refer to the form from time to time. However, When the form is displayed it disables the wheel on the mouse (which is quite a problem for me)... 1. Is there a way around this? 2. What else is different when a modeless form is displayed? Thanks M Tag: Record Macro Window. Tag: 981219
  - 3
    - Can i link various text in a cell to a picture I would like to link text in a cell to a picture so when i select or write text in a cell different pictures will appear next to the cell the text is in. Tag: Record Macro Window. Tag: 981217
  - 4
    - Thanks to Stephen Bullen for fixing VBA's miserable UserForm. With the qualification that I'm only using Win 2000 and this might've been fixed, and if I understand UserForm correctly: The seeming fact, which I just learned, that the highest-level visual control in VBA, UserForm, by design fails to implement even the most basic Windows features of being resizable, min- and maximizable (and I have yet to see a ScreenUpdate), has got to be the most pathetic, half- assed thing I've seen out of Redmond in a long time. UserForm is not a FORM. It has neither the appearance nor functionality of a form. It has little more than dialog functionality. I mean, when's the last time you've seen something on your Windows screen that's missing the Min/Max/Restore buttons, for God's sake? While they were at it, why didn't they omit caption functionality? Or force UserForm to be modal? (Thank God they didn't, but now I wonder: why not?) But can someone answer: what do users do who have screen resolutions different from the programmer's, when the box he designed doesn't fit their screen? As happened to me today when I ported my NonUserForm from one PC to another. I had to change in the form size IN DESIGN MODE. And, how in the world can all you programmers write an *application* in VBA? I REALLY hope I'm seriously missing something in my understanding here, I really do. It sure wouldn't be the first time. In fact, crow is one of the dishes I eat most gladly. *** In the meantime (and I can't believe it's taken me this long to discover it), I took Peter T's advice and went to Stephen Bullen's fabulous Excel site: www.oaltd.co.uk/Excel/Default.htm where there are all kinds of goodies. Not least of which is FormFun.xls, which includes all the necessary hooks out to Windows to fix the miserable, decrepit piece of crap that is UserForm. [Rant alert!] I have said this for decades: Microflot's Soviet approach to things will be its undoing. Because soon a company is going to come along with a computer on which you don't click a button called 'Start' to shut the goddamned system down. And by Soviet, I mean: Refusing to fix design flaws and their thousands of points of failure to implement their own--excellent-- Windows paradigms in EVERY user control, on EVERY screen, in EVERY context across their ENTIRE product spectrum. Yet, accomplishing that feat between even the most intimate siblings of the Office family is absolutely beyond this company. Instead, where do they spend their computing resources? Migrating us to operating systems requiring multiple cores and multi-gigabytes of storage, so we can have the thrill of executing a few MISERABLE machine instructions to do the things we did pretty well on our old 1981 IBM PCs--like balancing our checkbooks--but with 3D graphics, animation, and an electrical draw of a couple hundred more watts. If you have shares in MS, I say: maybe don't keep them for long. *** Tag: Record Macro Window. Tag: 981209
  - 5
    - Excel 2007 - How do I ... bring data into an excel 2007 worksheet that is stored in a sql database table? The idea is to parameterize the "where" clause in the sql select statement limiting the result set. There are lots of examples in the literature with regard to bringing in data stored in cubes --- but not so much from data in sql table. The basic issue is I want bring in descriptive information about say a contract (things like description, signing date, etc). Cubes which most (all) of the literature deals with are dealing with aggregations of amounts or "measures" and not so much about descriptions. As always all suggestions are welcome. Mark Tag: Record Macro Window. Tag: 981199
  - 6
    - UserForm ShowModal Problem I have a UserForm that will not Unload correctly for some reason when ShowModal = True. I get a Run-time Error '91': Object Variable or With block variable not set. But when I change ShowModal = False then everything Unloads properly. I have 8 other UserForms that have ShowModal = True and they all Unload just fine. Anybody know why this happens? Thanks in Advance, Ryan Tag: Record Macro Window. Tag: 981193
  - 7
    - code to extract data from a website runs of XP but not Vista I am working on code to extract data from a website. I am using CreateObject("InternetExplorer.Application") to start IE. The code is far from working perfectly but when I moved the code from an XP computer to a Vista computer I canâ??t get past the â??Do Until .readystate = 4â?? statement. IE opens visible and I get the following error message: â??The object invoked has disconnected from its clientsâ?? I did not get this error on the XP computer. The code is the following: Sub Tester() Dim objIE As Object Dim allTabs, t, x As Integer Dim r As Long, s As String, c As Long Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = False .Navigate "http://www.masters.org/en_US/scores/index.html" Do Until .readystate = 4 â??hangs on this statement on the Vista computer DoEvents Loop With .document Set allTabs = .getElementsByTagName("TABLE") For Each t In allTabs 'table with scores is always greater than 30 and only table that large on page If t.Rows.Length > 29 Then r = 2 For x = 1 To t.Rows.Length - 1 For c = 0 To 9 s = t.Rows(x).Cells(c).innerHTML ActiveWorkbook.Sheets("Info").Cells(r, c + 1).Value = s Next c r = r + 1 Next x End If Next t End With End With End Sub I have a feeling from looking up the error on the web that it may have something to do with the added security of Vista. Has anyone else run up against this problem? I see a lot of code samples here that use the same code to open IE that I do. -- russ Tag: Record Macro Window. Tag: 981192
  - 8
    - Automanipulating IE drop-down list in VBA/Excel I'm strictly a newbie in VB; I've been programming on the mainframe for decades but am only recently come to VBA, VBScript etc, so if you notice that I'm going about this all wrong, be sure and point me in a better direction. I have a spreadsheet that contains data from the web, and I'd like to make it smart enough to update itself when I tell it to. I've learned a little about the "Document" object model, and have gotten to the point where I can browse down through all the .Document.All items and figure out which ones I want VBA to manipulate. I can even fill in the values of web pages' text boxes and simulate a .Click on its buttons. But now I want to make a selection from a drop-down box. Let's say it has a list of states on it: I want my VBA program to pick out the correct state and convince the web page that I've chosen it. I've found the Option items that define the possible selections, and I see that if I find the correct one and change its Selected property to True, it shows up on the page correctly as though I selected it myself. But what the page doesn't do is proceed from that point as though I had clicked on the item - that is, it shows up in the drop-down selection box, but I can't get it to go forward from there. I've tried .Clicking on everything I can think of (might have missed one, though), but nothing seems to work. What am I supposed to do to get to the next step. If there's a FAQ on this, or another forum I should post to, just point me in the right direction. And if there's an easier way than using the Document object model - which wouldn't surprise me - feel free to point that out to me, too. Thanks. Tag: Record Macro Window. Tag: 981190
  - 9
    - Create a Macro with a List Hi Experts: > > I would like to build a macro with the List for multiple individuals that > will then enter the data from the List to the Summary Worksheet in the same > Workbook. This is the example of what I want to do.... > > List Sheet > > John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% > in Column D > > Put that data on the Summary Worksheet in Columns B, C, D & E > > I would also like to remove the % sign in Column D > > Can anyone help me with this? I would appreciate any assistance you can > provide. I'm working on a new reporting document that is using VLOOKUPs....a > lot of them every month....I think the Macro would be more efficient, but I > need your help to build on. -- jeannie v Tag: Record Macro Window. Tag: 981188
  - 10
    - I am not sure Hello; I am trying to help someone, for which I am not sure there exists a solution. He has a .csv file that he is opening in Excel. The values, somehow, import into the correct conglomeration of data. More in a minute. Column A is a number formatted as text. Column B is a series of numbers, some formatted as text. Those that are not formatted as text, are separated by commas (Huh!!) and hyphens. The object is to concatenate the two values together. Example: A | B | C | | 1 | 357 |Argentina | | 345 |32,34,39 |Paraguay | 40-45 | | | 33945 |27-30,41, | USA |43, 45 | The data should finally look like this for USA (etc.) A | B 3394527 |USA 3394528 |USA 3394529 |USA 3394530 |USA 3394541 |USA 3394543 |USA 3394545 |USA Which column the new values end up in does not matter. I know how to do this fairly rapidly with formula's and a big enough worksheet. Then copy and paste the values to where I want them. I am wondering if this is even possible. I am not a big VB fan. I like to avoid them if I can, but I wonder if this can even be handeled by VB. Column B on one of the cells looks like this as an example. 264235, 264469, 264476, 264497, 264536-264539, 264543, 264581-264584, 264724, 264729, 264772-264773 There is no regularity to the number of digits. There is no regularity to the hyphenation location. There is no regularity to the format of the numbers (which I guess all could be changed to numbers and back to text for concantenation) and there is no regularity to the quantity of values in the cell. Is there any hope for this? Any help would be appreciated. Thank You Frank Pytel PS. I see a lot of posts here that look really nice. I am on the Office Online site. I know these posts can be viewed and edited at other sites. Can I set this up in Outlook so that I can Post and search. Kind of like Outlook Express? Thanks Tag: Record Macro Window. Tag: 981185
  - 11
    - How to execute macro based on a users computer settings Hello experts, I'd like to find a method/script to only execute an excel macro based on a users existing file listing on their computer ( or it could be a pre-existing setting; ie. their IP or MAC address). Ideally I'd like to have a macro check for the existence of a certain file (or password phrase encoded in the file) on the directory of a users computer, if YES then execute macro, ELSE don't do anything. This is all done in the background right before the event that usually triggers the primary macro, which is an ftp command line script. My guess is creating a batch file that executes in a windows shell and outputs a value that could be read, but I'm not sure if that's the best way to achieve what I want. I want to avoid using a pop-up form and password inside the excel file - this is currently what I already have. Thank you for your help, -Doug Jr. Tag: Record Macro Window. Tag: 981182
  - 12
    - How best to break up code I hit the "procedure too large" error and I can't seem to get past it. I'm well aware of the reason and I can tell you know that I've effectively crammed all the IFs and LOOPs into it that I can, so let's assume that the code can't be condensed further. I've spent half-a-day reading through this group and Googling and can't seem to make it all come together. So my question is, how do I create this "well structured code" when I have SO MANY declarations which need to be used across the different procedures? I don't want to pass all of my declarations as arguments as I feel like that would take forever. There has got to me a more logical way that I am missing. And functions are supposed to be small aren't they? In reading the vba help, it seems that declaring the procedure or perhaps the individual variables using "static" would allow them to persist but they don't. Does it make a difference if I put everything in separate modules? From what I've read that isn't necessary. I read somewhere in this group that someone had a procedure that held all of their variables and they just called it in each procedure. How would this have been done? And if the variable is changed in the first procedure that calls it, then how can I get that value to persist to the next? I know the first question is going to be, let me see your code. But because this is more of a conceptual question, I'm leaving it out for now. I'll gladly provide some as needed. Many thanks for any assistance. Shelton Tag: Record Macro Window. Tag: 981179
  - 13
    - VBA Additional controls Hi all, I wanted to insert a spreadsheet on a form in excel 2003. So I went to tools additional controal and added spreadsheet 11.0. When I try and use this in my excel 2007 it says it can not find the object . In the additional controls there is no spreadsheet 11.0 is there way to add it ? a.so how can i find out what all the other additional controls do. Thanks Tag: Record Macro Window. Tag: 981175
  - 14
    - Controlling how a second excel file opens I have an Excel workbook I use to aggregate data from other excel workbooks. My macros copy data from the secondary workbook and appends the data to the primary workbook. My question: Is there the capability to open up secondary workbook without the secondary workbook executing any of it's macros and not do any calculations? Is there a better way to pull data from another excel workbook? Links are not an option. Thanks, - Pat Tag: Record Macro Window. Tag: 981174
  - 15
    - Checkbox I created a Userform with several checkboxes and comboboxes. Once all the information is filled out, i hit the run button. I'm getting an error because it's not reading if the checkbox is marked or not, it's always read as unmarked. What is the correct way of coding this? Tag: Record Macro Window. Tag: 981162
  - 16
    - Running Macros Remotely (.tcl file) I am trying to set up an automated macro routine via a .tcl "tickle" file (scheduled by the windows scheduler) to execute at a set time each day. When I execute the .tcl file below it will open the excel file but it errors out when it tries to execute the macro Error Msg : The macro may not be available in this workbook or all macros may be disabled. I can run the macro with no issues manually from the excel file. 1) does anyone have any idea what might be wrong ? 2) is there any other way to automatically run the macros at a given time ? Thanks, Grant TCL script: set application [::tcom::ref createobject "Excel.Application"] $application Visible 1 set workbooks [$application Workbooks] set workbook [$workbooks Open "D:/Profiles/r33998/Desktop/Working Parts Tracker/Split Out Tracking System/TCL testing.xls" 0 FALSE 2 sourcing] $application Run "TCL testing.xls!protect_worksheets_" #$workbook Save $application Quit Tag: Record Macro Window. Tag: 981154
  - 17
    - Combo Box and smart key selection Hi I have a spreadsheet (windows xp, MS Office 2003) with several userforms. One of these has a combo box, populated from the following code: Private Sub UserForm_Initialize() Dim myArr As Variant With Me.cbStdOP .ColumnCount = 9 'columns A:I .ColumnWidths = "180;0;0;0;25;0;0;0;0" 'hide the last 8 columns End With With Worksheets("AutoEntry") myArr = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _ .Resize(, Me.cbStdOP.ColumnCount) End With Me.cbStdOP.List = myArr End Sub This shows in the combo box only the column A (description) and column E (numerical codes) entries from the 'AutoEntry' worksheet. The user scrolls thru these and selects from these choices in the combo box, and the resulting data is placed into another ws ('Operations'). The rows in 'AutoEntry' are sorted by column E (numerical codes). I know this is not the best way to do this. I would like the user to be able to type the first numbers of the codes and have the combo box go to that selection (there are > 1500 rows to scroll through on the AutoEntry ws). Ideally, there would be a radio button to search by description OR by numerical code, and the combo box would show the matches. Thanks for any guidance! cls Tag: Record Macro Window. Tag: 981153
  - 18
    - Working with Arrays I am trying to use an array to move a group of sheets from one workbook to a new workbook. I know how to move them ALL at once to a new workbook using: Sheets(array("Sheet1", "Sheet2", "etc.")).Move However, only want to sent select sheets (selected by another, working, section of code). Here is my code, so far: ' define and setup variables Dim create_array, all_graphs Dim offset_var As Long Dim array_counter As Long offset_var = 0 array_counter = 0 ' this is a list of all the sheets that CAN exist all_graphs = Array("1.1.1 - LD AN Consumption", "1.2.1 - AN Amounts", "1.3.1 - Total AN Consumption", "2.1.1 - Tank Temperature", "2.1.2 - Fudge Point", "2.1.3 - pH", "3.1.1 - Blender Speed", "3.1.2 - Hot Cup Density", "3.1.3 - Viscosity", "4.1.1 - Truck Cup Densities", "4.2.1 - PE105 Calibration - AN", "4.2.2 - PE105 Calibration - Emu", "4.2.3 - PE105 Calibration - FO", "4.3.1 - PE106 Calibration - AN", "4.3.2 - PE106 Calibration - Emu", "4.3.3 - PE106 Calibration - FO", "4.4.1 - PE105 - Scale vs. Load", "4.4.2 - PE106 - Scale vs. Load", "5.1.1 - Tank Temperature", "5.1.2 - Fudge Point", "5.1.3 - pH", "6.1.1 - Blender Speed", "6.1.2 - Hot Cup Density", "6.1.3 - Viscosity", "7.1.1 - ANFO Test", "8.1.1 - Calibration - Unit 8018", "8.1.2 - Calibration - Unit 8025") ' there are a total of 27 potential sheets, however, only the ones with a value of 1, found at range_create.Offset(offset_var, 0), will actually exist While offset_var < 27 If range_create.Offset(offset_var, 0).Value = 1 Then create_array = all_graphs(offset_var + 1) End If offset_var = offset_var + 1 Wend ' move chart sheets to the end of a new workbook Sheets(create_array()).Move The section of my code that doesn't seem to work, at all, is Sheets(create_array()).Move. How do I implement this properly, or is there a problem with the previous section? I originally had it say: If range_create.Offset(0, 0) = 1 Then Sheets(Array("1.1.1 - LD AN Consumption").Move End If ' etc... all the way to the last one, but it only worked for the first one, and then crashed, it didn't want to add it to the same "new" workbook or another new one, which wasn't my needs anyways Thanks you Jon Peltier, Peter T., Dave Peterson, and ND Pard for all your help on that last section of code. I am still stuck working on it, but it is not necessary to complete at this time Tag: Record Macro Window. Tag: 981148
  - 19
    - Selfcert no longer working correctly I have used Selfcert successfuly for years. Recently it started asking to 'Enable Macros each time it was opened. I went to VBA Tools -> Digital Signature -> Choose and my signature when I chose it would not stick. Then I removed it and tried to use Selfcert to get it working again. Each time I get he message 'An error occurred during certificate creation. Selfcert was unable to create your certificate.'. It does this every time. I cannot find any reason for it, nor find any possible solution. TIA -- Gus Evans Tag: Record Macro Window. Tag: 981146
  - 20
    - Check access right to folder Hi Actually an easy one but I can't find anything on google. How can I check the access right to a folder (I just want to know if the user of the macro has access to a shared folder)? Is there any function that does that? (that would be on a network drive) thanks in advance Charles Tag: Record Macro Window. Tag: 981143
  - 21
    - Save Date How can I update the value in a cell that represents the last date the workbook was saved? Tag: Record Macro Window. Tag: 981137
  - 22
    - Userform: what happen to the sub who launched it? Hello quick question on userforms. If I have in the following sub something like sub LaunchUserform load userform1 userform1.show end sub what happens to the sub once the userform has been loaded and appeared on the screen? Is the sub terminated? If not at what point does the sub resumes? Same thing when I want to unload the userform, from a sub within the userform. Let's say that in one of the procedures contained in the userform I have: sub DoTheStuff if me.TheValue=1 then unload me end if msgbox "ok" unload me end sub I have the feeling it doesn't always unload the userform after the unload me, and that it doesn't interupt the sub DoTheStuff at unload me (I am working on a much more complicated macro, so it's not as easy to spot what's going on). How can I say both unload the userform and exit the sub at the same time? I don't know if this is related but I am also using a userform close sub which closes a DAO link and cleans up: Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Is there a chance that this is interfering with how the userform can be unloaded? The reason I ask is that sometimes when the userform gives the feeling it is done and gone, it looks like VBA is still active and the link to the DB not close (and I can interrupt the code by using the Reset button in VBA). So my ultimate question is something like is there an ultimate/propper/ ultra clean syntax or steps to follow to deal with userforms and loading/unloading them properly? If anyone has some lights on the topic, would be greatly appreciated. Thanks in advance Charles Tag: Record Macro Window. Tag: 981127
  - 23
    - Excel VBA Query Across Databases I'm trying to populate a recordset with 2 tables from seperate databases. So far, I get no error when I run the query, but it's also not populating the field from the second database (which happens to be the one I'm actively in). The query is below: With comboPubs ' Assign the Connection object. .ActiveConnection = cnPubs ' Extract the required records. .Open "select strName from db1_name..TblAnalysisOptions AO join db2_name..TblResultHeader RH on AO.guidAnalysisOptions=RH.guidAnalysisOptions" ' Copy the records into cell A1 on Sheet1. Sheet1.Range("A1").CopyFromRecordset comboPubs ' Tidy up .Close End With Is there anyway to do this? Both our on the same machine. Tag: Record Macro Window. Tag: 981117
  - 24
    - Copy rows from multiple workbook into a different workbook (sheet) Please I am very new to Macro. Please Help me on this one. I will really appreciate it. Will like to copy multiple rows from different workbook into one sheet in a different workbook. Each row has the same headings. How do i go about about to use macro. Again I haven't used it before. Please pardon my ignorance. Thanks for your help Tag: Record Macro Window. Tag: 981113
  - 25
    - Staff resource query Hi, How can I use the following input to populate the "Staff Count" as shown in the example below. Start Finish Person1 06:00 08:00 Person2 07:00 09:30 Time Staff Count 06:00 1 06:10 1 06:20 1 06:30 1 06:40 1 06:50 1 07:00 2 07:10 2 07:20 2 07:30 2 07:40 2 07:50 2 08:00 1 08:10 1 08:20 1 08:30 1 08:40 1 08:50 1 09:00 1 09:10 1 09:20 1 09:30 1 kind regards, Matt Tag: Record Macro Window. Tag: 981112

Hi,

I cannot see the small window which pop's up when we select record
macro from the option tools>Macro>

Can any one help tell me to to get it back so that i can record the
macro with or without refrence mode.

Regards

Heera

Top

RE: Record Macro Window. by GarysStudent

GarysStudent
Fri May 09 12:06:24 CDT 2008

View > Toolbars > Customize > Toolbars > and check Stop Recording

or put the following line in a macro and run it:

Application.CommandBars("Stop Recording").Visible = True

--
Gary''s Student - gsnu200785

"Heera" wrote:

> Hi,
>
> I cannot see the small window which pop's up when we select record
> macro from the option tools>Macro>
>
> Can any one help tell me to to get it back so that i can record the
> macro with or without refrence mode.
>
> Regards
>
> Heera
>

Top

RE: Record Macro Window. by James_Thomlinson

James_Thomlinson
Fri May 09 12:12:01 CDT 2008

Tools -> Customize -> Toolbars tab -> check Stop Recording Macro
This will bring up the toolbar. Record any macro and click stop on the
toolbar. This will hide the toolbar. Don't click the X. That disables the
toolbar and keeps it from appearing when you record macros.
--
HTH...

Jim Thomlinson

"Heera" wrote:

> Hi,
>
> I cannot see the small window which pop's up when we select record
> macro from the option tools>Macro>
>
> Can any one help tell me to to get it back so that i can record the
> macro with or without refrence mode.
>
> Regards
>
> Heera
>

Top

Re: Record Macro Window. by Heera

Heera
Fri May 09 12:58:18 CDT 2008

Gary & Jim Thank you.

That was a great help.....

Top