Application.Run

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
    - VB/Excel query To assist with office management, I need a VB script or excel macro that does not allow input in a defined column if the data being input is not listed on another column. For ex: the file has a sheet marked Operator Name, which lists names of all the staff members. The other sheet is an input sheet which requires us to input the name of the staff and their performance details. I need a VB script/excel solution that does not allow the inputter to input a name that is not listed on sheet marked Operator Name. (Data validation or List would not be appropriate as the team size is large and the inoutter will have to scroll through a huge list of names). Also, if I am not being too greedy, could it be possible for the script to mention the closest possible name available on the Operator Name sheet in case the inputter mis-spells the name. Thank you for your assistance. Regards, PD. Tag: Application.Run Tag: 981098
  - 2
    - Compile Error in Hidden Module: Module 1 I have a workbook that has code that works on my computer, and a couple of other computers. However, when the workbook is emailed to someone, then emailed back, I get the compile error above. I originally had the code password protected, but have since removed the password thinking that could be the problem - but that didn't work. the one thing I did notice was that the computer to which the file was sent has Windows SP3 installed, while the computer getting the error on return has SP2 installed. Could this have anything to do with it? Does anyone know to what "Hidden Module" refers to if the code is not password protected? Thanks Tag: Application.Run Tag: 981082
  - 3
    - Variable length named ranges I need a way to specify a variable for the last row in a named range. A static definition looks like this: ThisWorkbook.Names.Add Name:="dAge", RefersTo:="=RawData!$H$2:$H$100", Visible:=True I would like to replace â??100â?? with â??nRowsâ?? and need help with the syntax. Thank you. Tag: Application.Run Tag: 981077
  - 4
    - ms excel 2007 Filesearch I have the following code developed in Excel 2002. I have noted from this group that .Filesearch is not longer supported in ms excel 2007. I would like for this code to search for text "budget" in all workbooks and return the file and it's path to either a listbox or compile a list of all of the files names and path into an Excel Workbook. I am kind of lost on reworking this code with VBA in MS Excel 2007. Any suggestions you have would be greatly appreciated Sub findfiles() With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .TextOrProperty = "Budget" .MatchTextExactly = False .Filename = "*.xls" .Execute For i = 1 To .FoundFiles.Count UserForm1.ListBox1.AddItem.FoundFiles (i) Next i End With UserForm1.Show End Sub Tag: Application.Run Tag: 981070
  - 5
    - Math Operation: Set Subtraction I have two range objects, and I want to subtract range B from range A. That is, I want the opposite of Application.Intersect. This match concept is illustrated at http://www.mathwords.com/s/set_subtraction.htm I don't believe there's a single command for this, but can anyone provide some thoughts? I plan on approaching this using a loop, Application.Intersect, and Union() commands. Thanks, Matthew Pfluger Tag: Application.Run Tag: 981064
  - 6
    - Select Column based on Sheet Name I have a macro that will copy and add a sheet, and name it for each name in the first row. I now need to select the column in each sheet where the sheet name and header name are the same and set the background to gray. The number of columns and rows will vary. Is this possible to do? Thanks for all your help. I use this site a lot. Tag: Application.Run Tag: 981061
  - 7
    - Help with Grabbing Cell Value from Range This is the code I have: ' Grab the database name Dim strDb As Range Set strDb = Worksheets("Dashboard").Range("C2") Dim strDBString As String Set strDBString = strDb.Text The range is assigned without a problem; however, extracting the actual text value seems never to work. Can anyone clarify exactly how this should happen? My underlying motivation is to pull a cell value in VBA, and then use that in a SQL query within VBA as well. Tag: Application.Run Tag: 981055
  - 8
    - Sheet Protection Question Greetings, I have a worksheet with linking formulas in 79 cells (named ranges), Toolbars>Control Toolbox>CommandButtons and a Toolbars>Forms>ComboBox. This worksheet is set up as a form to view individual records chosen from the ComboBox. This form is setup to do one of two tasks. First view the record. And second, edit the record. To view: I need to only see the information in the record and not be able to change anything - until I click the "Start Edit" button. To edit: All of the formulas are changed into values. Then I can edit any or all of the cells.When finished, I click the "End Edit" button, which saves the edited version of the record into the "Customer List" sheet and then puts the formulas back into the cells. The only thing I am missing is how to keep from overwriting the formulas (in the view mode) with vba. The problem I ran into with Protect was that the CommandButtons were also protected and I need them to always be unprotected. Is there a way to lock everything but the CommandButtons and the ComboBox? I'm not even sure if protection is what I need. Security is not an issue here. Overwriting the formulas is. Another possibility would be if when the user entered any of the cells on that page, to automatically trigger the edit mode. The cells all have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79). Any help will be greatly appreciated. -Minitman Tag: Application.Run Tag: 981054
  - 9
    - Tracing where spreadsheet is opened from Dear All I am currently opening an existing Excel file from a macro within SolidWorks (thus written in VBA). Is there any way that when the spreadsheet opens, it checks to see where it was opened from? If it is opened from SolidWorks I would like it to launch a certain macro within the spreadsheet. Many thanks in advance Andy Tag: Application.Run Tag: 981053
  - 10
    - Retrieving the values of Excel check boxes in .NET I have an Excel spreadsheet which contains multiple un-grouped check boxes (from the Forms toolbar) which are not linked to any particular cell(s). I want to read their values in .NET code. For this I'm using 'Microsoft Office Interop Excel' and 'Microsoft Vbe Interop Forms' libraries. This is the code I'm using: String excelFile = "C:\\FileName.xls"; Excel.Application estimate = new Excel.Application(); Excel.Worksheet workSheet = new Excel.Worksheet(); estimate.Workbooks.Open(excelFile, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); workSheet = (Excel.Worksheet)estimate.Worksheets.get_Item("WorksheetName"); Excel.CheckBox cb = (Excel.CheckBox)workSheet.Shapes.Item("CheckBoxName").OLEFormat.Object; cb.Value returns the value of the check box (the value is -4146 if the check box is unchecked, 1 if it is checked and null if it is mixed). At some point in time I was able to see the names of all the check boxes in my worksheet (they were named "Check Box 1", "Check Box 2" and so on). But now I see that all the check boxes have the same name "CheckBox" which is very strange. This is making it impossible for me to retrieve the values of the check boxes by specifying the check box name. I know I can use the index also but it poses a similar problem - 'how do I know the index of each check box?'. Coming back to the name issue, is there any way I can see the unique names of all my check boxes again? Is there any mode which I have to use to be able to see the unique names? What am I missing here? Any help would be greatly appreciated. Thanks in advance. Tag: Application.Run Tag: 981050
  - 11
    - Userform Initialize Problem ! Dear Sir, I have a Userform for the data entry purpose, when I press the OK button then records are written on the sheet, and one message is appearing YES/NO. if I press Yes, then Userform must be clear all fields. but at the same time message appearing "Please use numbers only" infact I put the restriction on the Unit Price field, that should be numbers only. then I dont understand why this message is coming when I press OK button. any suggession ??? I AM USING THE BELOW CODE ========================= Private Sub CommandButton3_Click() ' for ok button ActiveWorkbook.Sheets("DailyPurchase").Activate Range("A5").Select ' to fill up combo box for the category If ComboCategory.ListIndex = -1 Then MsgBox "You must choose the category SHAHZAD" ComboCategory.SetFocus Exit Sub End If ' to fill up Date If DatePurchase = "" Then MsgBox "you must provide date" DatePurchase.SetFocus Exit Sub End If ' to fill up QTY If txtQuantity = "" Then MsgBox " you must provide Quantity " txtQuantity.SetFocus Exit Sub End If ' to fill up Unit price If txtUnitPrice = "" Then MsgBox " you must provide Unit Price " txtUnitPrice.SetFocus Exit Sub End If Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = DatePurchase.Value ActiveCell.Offset(0, 1) = TxtDescription.Value ActiveCell.Offset(0, 2) = txtQuantity.Value ActiveCell.Offset(0, 3) = txtUnitPrice.Value ActiveCell.Offset(0, 5) = ComboCategory.Value ActiveCell.Offset(0, 6) = txtPR.Value ActiveCell.Offset(0, 7) = txtRemarks.Value ' Range("A5").Select ' Ask and Do Procedure If MsgBox("One record is written, do you have more entries ?", vbYesNo, "Title") = vbYes Then Call UserForm_Initialize Else Unload Me End If End Sub Private Sub UserForm_Initialize() TxtDescription.Value = "" DatePurchase.Value = "" txtUnitPrice.Value = "" txtQuantity.Value = "" txtPR.Value = "" ComboCategory.Value = "" txtQuantity.Value = "" txtRemarks.Value = "" DatePurchase.SetFocus End Sub Private Sub txtUnitPrice_change() If Not IsNumeric(txtUnitPrice.Text) Then MsgBox "Please use numbers only" Exit Sub End If End Sub =================================== Please give me the solution, I dont need this message when I press OK button. Regards. Shahzad Tag: Application.Run Tag: 981049
  - 12
    - worksheet SaveAs WK4 in background I have an excel workbook with multiple sheets. I want to save one of the sheets as a WK4 in the background. I amusing the following code: Sheets("sheetx").SaveAs Filename:= "C:\xxx.wk4", FileFormat:=xlWK4, CreateBackup:=False The problem is that although the excel appears to still be open with all of the tabs, it has been renamed at the top to the WK4 filename. If I close the excel and open the WK4 then it opens as WK4 with only the sheet that I saved. My problem is that users may have an issue with the excel being "lost" while the supposed WK4 file looks like excel. SaveCopyAs does not seem to help because that cannot be done against a single sheet and cannot convert to WK4. Any ideas about how I could do a SaveAs only in the background without renaming the excel? Tag: Application.Run Tag: 981047
  - 13
    - Please help! I want to set up a condition where a row is deleted ONLY if BOTH cells equal zero. Currently the macro below delete the row when the first cell = zero. Thus when there is something in the second cell and the first cell is zero the row is deleted. The code is below if someone could assist I would be greatful. ' Sheets("Report").Select Cells.Select Selection.Copy Sheets.Add Cells.Select ActiveSheet.Paste Range("D11:H120").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Dim DeleteFlag As Boolean DeleteFlag = True While DeleteFlag = True DeleteFlag = False Range("A11:H120").Select For Each rw In Selection.Rows If rw.Cells(1, 4).Value = 0 And rw.Cells(1, 9) = 0 And IsNumeric(rw.Cells(1, 1).Value) And Not IsEmpty(rw.Cells(1, 1)) Then rw.Delete DeleteFlag = True 'rw.Cells(1, 10).Value = "Zero" End If Next rw Wend Application.CutCopyMode = False Range("D16").Select ActiveWindow.Zoom = 85 Range("A3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveSheet.Name = Right(Trim(Selection.Value), 5) Application.CutCopyMode = False Range("A3").Select Sheets("Macro1").Select Range("C2").Select Selection.Delete Shift:=xlUp Sheets("Report").Select Range("A3").Select ActiveCell.FormulaR1C1 = "=Macro1!R[-1]C[2]" Range("A3").Select Sheets("Data").Select Range("D4:E88").Select Selection.ClearContents End Sub Tag: Application.Run Tag: 981043
  - 14
    - Setting up Sum ranges inside a macro. When I try to set up a sum function in macro, the vertical range of the sum becomes an absolute range that does not expand when I try to add rows between the first and last row of the range set in the macro. The range does not expand. It seems to be anchored at the top of the range and only extends the number of cells down that was set in the macro. How do I set up a sum range that allows new rows to be added inside the range and make the range expand to include the new row of cells? Tag: Application.Run Tag: 981040
  - 15
    - Checkboxes with the same Group Name not Responding I have 4 Checkboxes in a UserForm. I have given all 4 Checkboxes the GroupName = "Shipping" in the Properties Window in the VBA Editor. We I Show the UserForm and click on one Checkbox the others do not turn off, why? Thanks in Advance, Ryan Tag: Application.Run Tag: 981033
  - 16
    - Identify shape in cell I have a sheet that was populated with data from a table copied from a website. One of the rows has cells which contain shapes rather than text. I need to convert these shapes to text and delete the shapes. Two questions: How can I take the current selection and determine just what shape is in it? How can I delete that shape after I set the value property to the desired text? Something like TypeName(Selection) will tell me that I have a shape if what is selected is the shape itself. I actually want to select the cell (or a range of cells) and, based on this, figure out what the shape might be. Thanks! -Martin Tag: Application.Run Tag: 981029
  - 17
    - Iterate rows of spreadsheet(s}\Get values I have two spreadsheets. The first one has a column with numbers in it. The second one has a column with the same numbers and a description column as well. Each number can have more than one description. What I want to do do is iterate the rows in Sheet 1 and lookup the numbers in sheet 2. Then return all the descriptions to sheet 1 and put them in the cell next to the number. If there are multiple descriptions for a number we would like them separated by a comma. Is there a function for something like this? TIA Tag: Application.Run Tag: 981027
  - 18
    - Help with match function, i need to tally Ok, I have thousands of rows of data. One of the columns is titled "age" and each row has a different age. Ranging from 15-100. I need to tally these rows into age group, 0-18, 19-30, 30-50 etc etc. Is there a code i can run that will tally these for me? Thanks, -Adam Tag: Application.Run Tag: 981020
  - 19
    - ADO help: Only get first 255 characters of field... Hi, can somebody help with this? I have an Access database, that has a table with a memo field in it. This table contains records longer than 255 characters. I am using ADO to get this info out of the database into a recordset, and then loop through the recordset to populate a spreadsheet. However, I only get the first 255 characters for the data that comes from the memo field. How do I get the entire value stored in the database? Somebody mentioned using "adLongVarWChar". But how do I do this? Please help, Thanks, Tag: Application.Run Tag: 981019
  - 20
    - distributing a spreadsheet with macro buttons How do you distribute an Excel 2003 spreadsheet application with buttons linked to macros? Do you actually have to relink all buttons to the macros for every new location? I can't believe that - I must be overlooking something. My spreadsheet has buttons which are linked to macros. When the spreadsheet is moved to a different location (on the same computer or a new computer), the following error occurs when a button is clicked: "A document with the name fubar.xls is already open. You cannot open two documents with the same name etc." I moved all macros into the Sheet1 Excel Object. That didn't help. I have tried using buttons from the control toolbox (which I read should correct this but doesn't), and from the forms toolbox, and the "custom menu item" from the macros list. Nothing works. When I put the spreadsheet on another machine, sometimes the buttons are there but I have to link them to the macros. Sometimes the buttons aren't there at all. I don't understand this and can't find anything to clarify it. Where are the toolbars stored? Why can't the buttons just look in the current workbook for the macros? Tag: Application.Run Tag: 981004
  - 21
    - Easy Worksheet Function help Where did i go wrong? =IF(F4='> 100',"1",)OR(F4='76 - 100',"2",)OR(F4='51 - 75',"3",)OR(F4='26 - 50',"4",)OR(F4='< 25',"5") Basicallyâ?¦ If F4 is equal â??> 100â?? then display a 1 or, if F4 is equal to â??76 â?? 100â?? then display a 2 or, if F4 is equal to â??51 â?? 75â?? then display a 3 Tag: Application.Run Tag: 981003
  - 22
    - UDF Function to calculate mean moving range (control charts) I was about to post a question as I was trying to create a function that would calculate the moving mean range which I would then be able to use to calculate the UCL and LCL for a series of data i.e Mean + 2.66*Moving mean range, without having to calculate all of the variances between each 2 sets of data....hope that makes sense. However, I have cracked it and wanted to post the Function in case anyone else wants it: It's pretty simple stuff as it is the first user defined function I've written so apologies if this is clumsy code.... Function MMR(CalledCells As range) As Variant Application.Volatile True Dim V1 As Variant Dim V2 As Variant Dim V3 As Variant Dim i As Integer Dim FirstRow As Long Dim LastRow As Long V1 = 0 FirstRow = CalledCells.Row LastRow = CalledCells.Row + CalledCells.Rows.Count - 1 For i = LastRow To FirstRow + 1 Step -1 V2 = Abs(Cells(i, CalledCells.Column).Value - Cells(i, CalledCells.Column).Offset(-1, 0).Value) V3 = V2 + V1 V1 = V3 Next i MMR = V1 / (CalledCells.Rows.Count - 1) End Function Tag: Application.Run Tag: 980999
  - 23
    - Trying to create Macro to delete duplicate Rows Hello, I am hoping someone can help me. I am not an experienced macro writer and I am having a problem on how to write a macro on deleteing duplicate rows. To give you an example of what i am trying to do is: I am trying to delete rows with duplicate information that is in column A,S,T,U,V,W, Y, and Z. But i want to leave at least one row with that info. For example if row 1, 2, and 3 have the same info in A, S, T, U, V, W, Y, and Z. I would want to leave row 1 and delete row 2 and 3. I am hoping someone can help. Thanks Tag: Application.Run Tag: 980988
  - 24
    - Optimising Code I have the following that is part of a larger piece of code that formats a worksheet. Currently, this part takes 15 minutes to run and I would like to know if I could have written this code in a better way: For Each c In r If c.Value = "Complete" Or c.Value = "Not Applicable" Then With Cells(c.Row, c.Column - 28) .ClearFormats .Interior.ColorIndex = 5 .Font.ColorIndex = 2 .Font.Name = "Verdana" .Font.Size = 8 .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .NumberFormat = "dd/mm/yy" End With End If Next This is running on a area that is 203 rows x 17 columns, the number of rows varies. I cannot use conditional formatting as it already exists in the target cells and has to be removed and reformatted as above. Unless there is a better way, of course! Many thanks for any suggestions Louise Tag: Application.Run Tag: 980987
  - 25
    - Multiple checkbox selection I have an excel sheet with many Yes/No, Good/Abnormal checkboxes. The list is long. I want to have this checksheet have a feature to select all Yes, or Select all no, or select all good, select all abnormal, to avoid the operator having to check all if good/bad/ or the other. Any suggestions Tag: Application.Run Tag: 980984
- Next
  - 1
    - Excel2000: Avoiding loop in Worksheet_Change event Hi I have a Change Event for a worksheet, where 2 cells on this worksheet are checked, and some actions are taken, whenever some of them changes. By certain conditions, one of actions will be changing the entry in one of checked cells - which will fire the same Change event again, etc. Is there a way to 'switch the Change Event firing off' temporarily. I mean, that when the condition for changing this cell value is met, some code row has to switch executing of further Change events off, after that the change to cell is made, then further change events are turned on again, and then rest of code in current Change event is processed. Tag: Application.Run Tag: 980981
  - 2
    - Automation add-in - UDF to multi cells... Hi all, I have been doing some reading on this forum and could not find exactly what i was looking for so i decided to post. I have an automation add-in written in C#. I need a UDF function to populate multiple cells with data. (like a table of data, and the cell containing the UDF function would be the top left corner of the 'rendered' table). Is this possible? I have read that a UDF function can only return and populate a single cell. Well there must be some way to do it as I have seen other add-ins do this, and im trying to replicate the behaviour. Thanks! Dale *** Sent via Developersdex http://www.developersdex.com *** Tag: Application.Run Tag: 980977
  - 3
    - List of email addresses Hi I have a column with about 40 email addresses one below the other (i cell for each address). I would like to run a macro on the range so that all the addresses are combined in the one cell prefereably in the adjacent column. There is a semicolon at end of each address so I dont have to worry about the data ending up in one cell. Any help appreciated on this. Thanks, Aidan. Tag: Application.Run Tag: 980973
  - 4
    - Open several files and copy the contents I have a template that requires new data from 4 files. I want to open each in turn and copy the contents into specified sheets in the template. Each file is created by another program that always uses the same file name. The users add an extension to the name for each company they are working on to keep htem unique. I have the following code that opens the first file and copies the new data okay. Is there a way to modify this to use the one user input for each file and copy the data in turn to each worksheet? Sub OpenFile() Dim myFileName As Variant Dim wkbk As Workbook Dim MyPath As String Dim sFilename As String Dim fExitDo As Boolean Dim sFileType As String Dim sFileOpen As String Dim sFileBudget As String Dim sFileJobList As String Dim sFileOrders As String Dim sFileLedger As String Dim Msg, Style, Title, Help, Ctxt, Response, MyString Msg = "Select ""YES"" to proceed to Open WIP Data Files, ""NO"" to view Current File only" Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Open New WIP Data Files " ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then sFileBudget = "jobba1-" '--Job budget file to copy to sheet "Budget" sFileJobList = "jobbl1-" '--Job Job List file to copy to sheet WIP" sFileOrders = "salcustd1-" '--Job Customer Sales Orders file to copy to sheet "Orders" sFileLedger = "genjrld1-" '--Job General Ledger Detail file to copy to sheet "Ledger4900" MyPath = "S:\MYOB Data Files\WIPData\" ChDrive "S:\" ChDir MyPath '--users are to add a file name extension to the standard reports and save as .XLS files. It will be same for all 4 data files sFilename = InputBox("Please Provide ONLY the Name you saved the file as. EG: DEMO") FilesInPath = Dir(MyPath & "*.xl*") 'Check to see if files exist (Ron DeBruin) If sFilename = "" Then Exit Sub 'user hit cancel If FilesInPath = "" Then MsgBox "No files found-make sure you have saved your files in the correct location" Exit Sub End If End If sFileOpen = MyPath & sFileBudget & sFilename & ".xls" fExitDo = False Set wkbk = Workbooks.Open(Filename:=sFileOpen) Else Exit Sub End If ActiveSheet.Cells.Select Selection.Copy Application.DisplayAlerts = False Windows("WIP Template V1.xls").Activate Sheets("Budget").Select Cells.Select ActiveSheet.Paste wkbk.Close Savechanges = False Application.DisplayAlerts = True End Sub -- Jim Tag: Application.Run Tag: 980967
  - 5
    - One Workbook to Several Workbooks I have been using this Ron de Bruin code all day long! i've learned a lot today and now, before I turn in, I'm trying to get the code to do one more trick for me, but I can't figure out how to do it. Basically, the code below splits a large Workbook into several files, each with just one sheet. I would really like to split the large Workbook into several smaller files, and attach a summary sheet to each newly created file. The summary sheet is named 'Sheet1'. Is there some way to append a Sheet1 to each newly created file? Sub Copy_Every_Sheet_To_New_Workbook() 'Working in 97-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim sh As Worksheet Dim DateString As String Dim FolderName As String With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With 'Copy every sheet from the workbook with this macro Set Sourcewb = ThisWorkbook 'Create new folder to save the new files in DateString = Format(Now, "yyyy-mm-dd") FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString MkDir FolderName 'Copy every visible sheet to a new workbook For Each sh In Sourcewb.Worksheets 'If the sheet is visible then copy it to a new workbook If sh.Visible = -1 Then sh.Copy 'Set Destwb to the new workbook Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 If Sourcewb.Name = .Name Then MsgBox "Your answer is NO in the security dialog" GoTo GoToNextSheet Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want If Destwb.Sheets(1).ProtectContents = False Then With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False End If 'Save the new workbook and close it With Destwb .SaveAs FolderName _ & "\" & Destwb.Sheets(1).Name & FileExtStr, _ FileFormat:=FileFormatNum .Close False End With End If GoToNextSheet: Next sh MsgBox "You can find the files in " & FolderName With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With End Sub Regards, Ryan--- -- RyGuy Tag: Application.Run Tag: 980966
  - 6
    - How do I exit from a macro when a condition is not met In Excel 2003 I have a macro that checks if a cell is not zero. Range ("P124").Select If Selection.Value <> 0 Then Call Check End If Sub Check() Dim Msg, Style, Title, Response Msg = "This JV does not balance, please correct it before you continue." Style = vbOKCancel Title = "Out of Balance Response = MsgBox (Msg, Style, Title) End Sub I want to exit out of the macro routines when the user presses OK. Thanks Andy Tag: Application.Run Tag: 980963
  - 7
    - Userform coding problem I have a userform in which two values are input. What I want to do is on exit from the second text control (txtPurAmt) is clear the current data, set the focus in the date field and be ready to input another record. What is happening is that after I set the focus on the txt Date control, the code wants to run again. Here is what I have so far and have commented where the problem occurs. Any help is greatly appreciated. Thank you. Private Sub txtPurAmt_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim strAgain As String, bYes As Boolean Range("A9").End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveCell.Value = Me.txtDate.Value ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.Value = CCur(Me.txtPurAmt.Text) strAgain = InputBox("Add another record?", "Add New Record?", "Yes") If strAgain = "Yes" Then ActiveCell.Offset(1, -1).Select Me.txtDate.Value = "" Me.txtPurAmt.Value = "" bYes = True Else Me.txtDate.Value = "" Me.txtPurAmt.Value = "" bYes = False End If If bYes = True Then '************ 'After the next line, the code starts all over again and the 'Exit Sub line never executes. Me.txtDate.SetFocus Exit Sub Else UserForm1.Hide Exit Sub End If End Sub Tag: Application.Run Tag: 980959
  - 8
    - Upgrade PivotTable drop-down selection box Thanks in advance for any help! I'd like to essentially improve upon the PivotTable drop-down selector for PivotFields. I use Pivots that have so many options to choose from that you end up having to click the box, unclick 'show all' and then dig for the items you want. I basically just want to be able to type in the one I want to choose, or type a portion of it and filter the available items. The ideal situation would be to completely override the current selection box with my own code. That may be impossible or beyond my ability. If it is possible, please give me a reference or point me in the right direction with the code. If that's not possible... I'm looking for design ideas. I haven't yet come up with a great idea on the implementation that will be easy to transfer to other people without having to spend much time on training them to use it. I want this to be applicable to PivotTables across the board (i.e. a modification to PivotTables in general, not just one particular spreadsheet). Thanks again for any help or ideas you can provide. Tag: Application.Run Tag: 980957
  - 9
    - Grab all filtered rows for some action In an autofiltered range, I currently select all the filtered rows for deletion manually. Is there a way to automate this manual selection of filtered rows bit once I select any cell within the filtered rows? Thanks Tag: Application.Run Tag: 980934
  - 10
    - Macro defining dynamic named ranges At times I need to change the source data used in some formulas. When this happens I would like to remove the source data sheet, called RawData, and import a new sheet that contains current data. I am trying to write a macro that would define named dynamic ranges on RawData. Iâ??m using this code I copied from some other post: ThisWorkbook.Names.Add Name:="dAge", RefersTo:="=OFFSET(RawData!$H$1,0,0,COUNTA(RawData!$H:$H),1)", Visible:=True This line doesnâ??t leave any trace in the Name list; Insert>Name>Paste>Paste List does not show â??dAgeâ??, nor does a loop with â??Debug.Print crtName.Name, crtName.RefersTo, crtName.Visibleâ?? Even worse, this simpler one doesnâ??t work either ThisWorkbook.Names.Add Name:="dAge7", RefersTo:="=RawData!$H$1:$H$10", Visible:=True When I add the name with Insert>Name>Define this works: =OFFSET(RawData!$H$1,0,0,COUNTA(RawData!$H:$H),1) I would appreciate any help with this syntax or other suggestions for updating/defining dynamic ranges in VB. Thank you. Tag: Application.Run Tag: 980931
  - 11
    - Drop-Down Arrow in AutoFilter Hi How can I programatically click the drop-down arrow in an autofilter? Geoff Tag: Application.Run Tag: 980929
  - 12
    - Running code. Hello, I have been using VB and C# in Visual Studio for a long time but never used it with Excel. I have an Excel sheet with 16 images. Each one in each cell and having a name of img1, img2, .... I have some VB code which I want to run. When I run it am I asked for a Macro name. Could someone, please, give me some tips on how to run this game code? Here is the code: Option Explicit ' TYPES > ' GameType Type GameType img(1 To 6, 1 To 6) As String LastRow As Integer LastCol As Integer Score As Integer End Type ' GameType ' VARIABLES > Public game As GameType ' METHODS > ' MyWait Sub MyWait(ByVal wt As Single) Dim t As Single t = Timer Do Loop Until Timer - t > wt End Sub ' MyWait ' Positioning Sub Positioning(ByRef game As GameType, ByVal r As Integer, ByVal c As Integer) Dim hc As Single, hf As Single, wc As Single, wf As Single Dim nf As String On Error GoTo fim With Range("tabuleiro") hc = .Cells(r, c).Height wc = .Cells(r, c).Width nf = game.img(r, c) hf = ActiveSheet.Shapes(nf).Height wf = ActiveSheet.Shapes(nf).Width ActiveSheet.Shapes(nf).Top = .Cells(r, c).Top + (hc - hf) / 2 ActiveSheet.Shapes(nf).Left = .Cells(r, c).Left + (wc - wf) / 2 End With fim: End Sub ' Positioning ' Hide Sub Hide(ByRef game As GameType, ByVal r As Integer, ByRef c As Integer) With game ActiveSheet.Shapes(.img(r, c)).Visible = False End With DoEvents End Sub ' Hide ' Show Sub Show(ByRef game As GameType, ByVal r As Integer, ByRef c As Integer) With game ActiveSheet.Shapes(.img(r, c)).Visible = True End With DoEvents End Sub ' Show ' IsVisible Function IsVisible(ByRef game As GameType, ByVal r As Integer, ByRef c As Integer) As Boolean With game IsVisible = ActiveSheet.Shapes(.img(r, c)).Visible End With End Function ' IsVisible Thanks, Miguel Tag: Application.Run Tag: 980921
  - 13
    - Automatically Change Row Height Based on Cell Contents? Is there a way to change the row height based on the contents of a cell? Here's what I've done: 1. I have a worksheet with about 6,000 or so lines, containing data for over 100 departments. 2. I've created Subtotals, and used Ron DeBruin's code to create separate worksheets for each department. 3. The subtotals came over to the individual worksheets just fine. Now I want to change the row height of the rows that contain the subtotals to 25, so the worksheets are easier to read. Each of the subtotal rows has the word "Total" included in the entry in column B. Is there a way to automate this task? Thanks, Sam. Tag: Application.Run Tag: 980918
  - 14
    - Adding a divider to an add-in using vba in excel 2007 I have done some research and thought that I had this figured out, but it is not giving me the result that I am looking for. I am trying to add a divider to a drop down list between different macros. I thought this was done by the BeginGroup property, but it is not adding the divider. I have posted a section of my code below. Can anyone tell me if I am going about this wrong? Any help would be great. Thanks, Jason Set cmdBar = Application.CommandBars("Worksheet Menu Bar") Set cmdBarMenu = cmdBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmdBarMenu .Caption = "Tools" .Tag = "JJT" End With Set cmdBarMenuItem = cmdBarMenu.Controls.Add(Type:=msoControlButton) With cmdBarMenuItem .Caption = "Merge" .OnAction = "MergeCells" .Tag = "JJT" .FaceId = 798 End With cmdBarMenu.BeginGroup = True Set cmdBarMenuItem = cmdBarMenu.Controls.Add With cmdBarMenuItem .Caption = "Unmerge" .OnAction = "UnmergeCells" .Tag = "JJT" .FaceId = 800 End With Tag: Application.Run Tag: 980917
  - 15
    - Problem in excel - possible MACRO solution ? I got this file with the trivia questions and answers. The sheet in excel looks something like this : A column : Question 1 a) Answer 1 b) Answer 2 c) Answer 3 d) Answer 4 Question 2 a) Answer 1 b) Answer 2 ... B column : + mark in the row where the answer to the question is right. My question is : can I make a new sheet with the question numbers and the right answers only ? It should look something like this : 1 a 2 b 3 d 4 c 5 d ... Ty all in advance ! Marko Tag: Application.Run Tag: 980913
  - 16
    - Counting and adding values int two different columns I'm developing a basic Life Cycle Cost Analysis Spreadsheet and need some assistance adding the costs of particular activity into a specified year. Example: This column checks if the Cash Flow Year is listed in the "Year Cost Occurs" and and then adds it to the other rows that apply for a total cash flow year. Cost$ Year Cost Occurs | Cash Flow Year Total in Cash Flow Year 300 0,2,4,5 | 0 =330+225 225 0,1,3,5 | 1 =225+125 125 1,2,3,4,5 | 2 =125+300 3 =225+125 4 =300+125 5 =300+225+125 I am hoping someone can have some code that will look at the Year cost occurs and then add it to the Total in Cash Flow Year. Currently, this is the code I use =IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$8&",")),'Alternative 1'!$G$8, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$9&",")),'Alternative 1'!$G$9, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$10&",")),'Alternative 1'!$G$10, 0) It applies to two different sheets but the lenght of the string is limited so my formula is basically too long. Any suggestions to create a loop through an array function? Thanks Miguel Tag: Application.Run Tag: 980907
  - 17
    - Adding items in one row if criteria in another column is met. I'm developing a basic Life Cycle Cost Analysis Spreadsheet and need some assistance adding the costs of particular activity into a specified year. Example: This column checks if the Cash Flow Year is listed in the "Year Cost Occurs" and and then adds it to the other rows that apply for a total cash flow year. Cost$ Year Cost Occurs | Cash Flow Year Total in Cash Flow Year 300 0,2,4,5 | 0 =330+225 225 0,1,3,5 | 1 =225+125 125 1,2,3,4,5 | 2 =125+300 3 =225+125 4 =300+125 5 =300+225+125 I am hoping someone can have some code that will look at the Year cost occurs and then add it to the Total in Cash Flow Year. Currently, this is the code I use =IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$8&",")),'Alternative 1'!$G$8, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$9&",")),'Alternative 1'!$G$9, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$10&",")),'Alternative 1'!$G$10, 0) It applies to two different sheets but the lenght of the string is limited so my formula is basically too long. Any suggestions to create a loop through an array function? Thanks Miguel Tag: Application.Run Tag: 980905
  - 18
    - Warning message while macro runs Is there a way I can display a message while a macro runs in the background? I want to let users know to wait until the macro finishes. I tried the msgbox command but the macro will not run until you press ok and I want it to stay open until the macro finishes. Any ideas? Tag: Application.Run Tag: 980900
  - 19
    - Option Button FaceID in a custom commandbar I have created a custom command bar, and inserted a pair of option buttons from the forms toolbox. They link to Macros in my code exactly as you would expect. The problem is that I can't figure out how to change the filled dot to an empty dot, ie how to indicate a deselected option. The controls has a .Status property which can be set to msoButtonUp and msoButtonDown, but that isn't actually what I want. I have looked through all 5000 FaceIDs looking for an empty option button, but can't find it (the idea being to toggle the control FaceID through 2 different values). I am beginning to think that it's done some other way, but none of the properties look likely, and the online help seems to be silent on the subject. I have tried Google to no avail too. I can make do with toggling between FaceIDs 1086 and 1087 (green tick, red cross) but that isn't quite the emphasis I want to place on the options. I tried putting a Forms Toolbox option button in the worksheet and looking at it's FaceID, but couldn't even find a FaceID in the watch window. , so that was a dead-end too. What am I missing? Tag: Application.Run Tag: 980899
  - 20
    - Sum Column Y at first blank-bottom cell. The code below fails on this line: sumRng = ActiveSheet.Range("Y2:Y" & lastYRow) The balloon in the VBE says sumRng =Nothing What am I doing wrong? Sub Math() Dim lastRow As Long Dim c As Variant Dim sh As Worksheet Dim myA As Range Dim lastYRow As Long, sumRng As Range, Tot As Double For Each sh In Worksheets If (sh.Name) <> "Sheet1" Then sh.Activate Rows("1:1").Select Selection.Font.Bold = True lastRow = Cells(Rows.Count, "F").End(xlUp).Row For Each c In Range("F2:F" & lastRow) If c.Value <> "" Then c.Offset(, 19).Value = "=RC[-17]*RC[-2]" End If Next c lastYRow = Cells(Rows.Count, "Y").End(xlUp).Row sumRng = ActiveSheet.Range("Y2:Y" & lastYRow) Tot = Application.WorksheetFunction.Sum(sumRng) MsgBox Tot ActiveSheet.Rows(lastRow + 1) = Tot Rows("2:2").Select Selection.Delete Shift:=xlUp End If Next sh Sheets("Sheet1").Select End Sub Regards, Ryan--- -- RyGuy Tag: Application.Run Tag: 980897
  - 21
    - dual zero conditional row deletion I want to set up a condition where a row is deleted ONLY if BOTH cells equal zero. Currently the macro below delete the row when the first cell = zero. Thus when there is something in the second cell and the first cell is zero the row is deleted. The code is below if someone could assist I would be greatful. ' Sheets("Report").Select Cells.Select Selection.Copy Sheets.Add Cells.Select ActiveSheet.Paste Range("D11:H120").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Dim DeleteFlag As Boolean DeleteFlag = True While DeleteFlag = True DeleteFlag = False Range("A11:H120").Select For Each rw In Selection.Rows If rw.Cells(1, 4).Value = 0 And rw.Cells(1, 9) = 0 And IsNumeric(rw.Cells(1, 1).Value) And Not IsEmpty(rw.Cells(1, 1)) Then rw.Delete DeleteFlag = True 'rw.Cells(1, 10).Value = "Zero" End If Next rw Wend Application.CutCopyMode = False Range("D16").Select ActiveWindow.Zoom = 85 Range("A3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveSheet.Name = Right(Trim(Selection.Value), 5) Application.CutCopyMode = False Range("A3").Select Sheets("Macro1").Select Range("C2").Select Selection.Delete Shift:=xlUp Sheets("Report").Select Range("A3").Select ActiveCell.FormulaR1C1 = "=Macro1!R[-1]C[2]" Range("A3").Select Sheets("Data").Select Range("D4:E88").Select Selection.ClearContents End Sub Tag: Application.Run Tag: 980896
  - 22
    - stuffing japanese text into a variable I have a spreadsheet that has Japanese names in a range of cells. I would like to stuff the Japanese into a variable and move the contents of the variable -- call it NAME -- to another application. Whenever I try to assign the japanese text to NAME, I generate an error -- run- time error 1004. I do not believe the text is unicode. I have dimmed NAME as string. Should I change it to another data type, if so, which one? Is there a way to convert my japanese text to japanese unicode? as always, thanks in advance ... Tag: Application.Run Tag: 980892
  - 23
    - Pivot table not recognizing same field values I created a pivot table in Excel 2003 that uses five field list on a sheet in the same workbook. The fields are Promotion, Paper, Amt, Month, and PromoCategory. The pivot table is returning multiple records for what appear to be identical values in the PromoCategory field. I consolidated data from January through March from a 70-column wide sheet that staff had been using. For that period, and one record in the pivot is returned for the January through March data and another for the newly entered April data. The early data has no value in the promotion field (which is not used in the pivot). The April data--but not the early data--uses a vLookup function to populate the PromoCategory field from the Promotion value. I can group the pivot table records where the PromoCategory produces two rows, but it is time consuming and the enduser is not ready to do this each month. And even when grouped, the final report, which uses GetPivotData, can't seem to find the newly grouped data. Tag: Application.Run Tag: 980891
  - 24
    - Find & Copy Files Listed in Excel Worksheet ? I have a list of filenames (complete with file extensions) in a single column in an Excel Worksheet. All of these listed files are in the same folder (along with many other files) on the hard drive. Each filename in the list is hyperlinked to the actual filespec (drive, folder, filename). The list may contain several hundred filenames. How do I read the list of filenames, find the actual files, and copy them all at once into a separate folder on the hard drive? I can manually search with Win Explorer and select each file using Ctrl-Click & mass copy using Paste, but its very laborious. Is there a commercial software product to accomplish this job? I'm using Win Vista Home Premium & Excel 2007. Also have XP Pro & Excel 2003 available. Tag: Application.Run Tag: 980888
  - 25
    - Find and Replace Question If I have a column that has both numbers and words is there a way to replace the words with an alternate value? For example the column may read: 123456 1234567 adam 4569 eve 111-111 If I were to sort the above column the "adam", "eve", and "111-111" would sort as words and not numbers obviously. I want to write a macro to look at the column and take all of the word entries, ignore the numbers and replace with another word, in this case "Check". When finished the above would read: 123456 1234567 Check 4569 Check Check I found some references here about isnumeric and istext and variations of this but I was not able to make it work for what I was attempting. Thanks for any help. Tag: Application.Run Tag: 980874

Hello,
I have been looking at Application.Run
I see the following code in MSDN.

In this example they use a template name.
Does that mean a template can be not opened? And it will run a method from
any template?

How about an add-on?
how about an xla?

can you have:
Application.Run MacroName:=<<xla>>& "." _
& <<module1>> & "." & <<some method>>

??

strTemplate = InputBox("Enter the template name")
strModule = InputBox("Enter the module name")
strMacro = InputBox("Enter the macro name")
strParameter = InputBox("Enter a parameter value")
Application.Run MacroName:=strTemplate & "." _
& strModule & "." & strMacro, _
varg1:=strParameter

Top

Re: Application.Run by Peter

Peter
Thu May 08 13:49:41 CDT 2008

Sure -

sMacro = "myAddin.xla!mod_Procs.myMacro"
Application.Run sMarcro, arg1

Regards,
Peter T

"greg" <iuouh@ghfhg.com> wrote in message
news:ebQA4WTsIHA.2064@TK2MSFTNGP05.phx.gbl...
> Hello,
> I have been looking at Application.Run
> I see the following code in MSDN.
>
> In this example they use a template name.
> Does that mean a template can be not opened? And it will run a method
from
> any template?
>
> How about an add-on?
> how about an xla?
>
> can you have:
> Application.Run MacroName:=<<xla>>& "." _
> & <<module1>> & "." & <<some method>>
>
> ??
>
> strTemplate = InputBox("Enter the template name")
> strModule = InputBox("Enter the module name")
> strMacro = InputBox("Enter the macro name")
> strParameter = InputBox("Enter a parameter value")
> Application.Run MacroName:=strTemplate & "." _
> & strModule & "." & strMacro, _
> varg1:=strParameter
>
>

Top