Programmatically Halt Macro and Open VB Editor

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
    - I am new to VBE. How can I create a for loop without typing it into Let's say I have a column with the number 1-100. I want to scan thru each number until I hit the number 70. Is there a graphical way to do this or must I know how the VBA syntax and type it in the code? Tag: Programmatically Halt Macro and Open VB Editor Tag: 968701
  - 2
    - AutoFit Row Size with Merged Cells I need to add text from a Userform Textbox to a worksheet. Sometimes the textbox may conatin a few sentences. I would like my code to put the textbox text into the merged cells, wrap the text to fit, and automatically adjust the row height. Is it possible to do this or is there an easier way? The text has to be contained between Col.C and Col.E. Sub AutoFitText() With ActiveCell 'ActiveCell = Range("B10") in this case '.....I have additional code here .Offset(30, 1).Value = " â?¢ Comments: " & tbxComments With Range(.Offset(30, 1), .Offset(30, 3)) .Merge .HorizontalAlignment = xlLeft .VerticalAlignment = xlCenter .WrapText = True .Rows.EntireRow.AutoFit End With '.....I have additional code here as well End With Thanks in Advance, Ryan Tag: Programmatically Halt Macro and Open VB Editor Tag: 968697
  - 3
    - creating a macro to find a cell value and return a statement in a Hi Can someone please help me create a macro to search an Excel worksheet(in column B) for a cell value("42285") and return a statement("European Trade") in column "E". The data can always change and the rows of data can be up to 3,000 rows. I have the below code but it needs alot of work. Dim rowCount As Integer rowCount = ActiveSheet.UsedRange.Rows.Count Dim i As Integer For i = 1 To rowCount Range("B" & i).Select If ActiveCell.Value = "42285" Then GoTo loopAgain Else ActiveCell.Offset(0, 4) = Sheets("Sheet1").ActiveCell.Offset(6, 4) ' write code to do whatever(return your activecell.offset value) End If loopAgain: Next i End Sub Thank You Peter Tag: Programmatically Halt Macro and Open VB Editor Tag: 968695
  - 4
    - VBA Code needed to AUTOMATE VALIDATION selections and then PRINT EACH I have searched the blogs and cannot seem to locate anything related to my challenge. I know you guys are gurus when It comes to things like this. I have a worksheet that tracks employee productivity for each cost center with a region. My excel worksheet contains three validation dropdowns that use the "indirect" function with various named ranges that link these dropdowns . So for instance lets say that the first dropdown relates to a region, the second relates to a cost center (there can be many cost centers in a region), and the third is a worker in that cost center (there can be many workers in a cost center). So, lets say that I select Northwest Region =E0 10001 (Cost Center) =E0 and Jim Stu, John Doe, Bob Doe, and Jane Done can all be selected from this cost center individually which changes the productivity data for each worker. Drop Down 1 (region) --> Drop Down 2 (cost center) -> Drop Down 3 (Worker) Range1 =3D Region Range2 =3D (indirect function from cell in Range 1) Range3 =3D (indirect function from cell in Range 2) What I would like to do is automate the printing process of the workers within a selected cost center using these already defined ranges. So for example in the example above I want to print all four names dynamically if some one decides that they would like to print all of the workers in ANY cost center. I am looking for the VBA code for this endeavor. Is this feat possible without having to select each and every worker within that cost center? Also, to take it a step further, is it possible to select all workers from every cost center and print all cost centers at once? Tag: Programmatically Halt Macro and Open VB Editor Tag: 968694
  - 5
    - Userforms fill screen whatever resolution Hi, I am designing a system that I want to look like it is not an Excel spreadsheet. I would therefore like the UserForms to fill the screen. I can do this manually but if somebody else has a different screen resolution it doesn't fit right. Is there a way of doing this automatically? Can you use an if to determine screen resolution? Thanks in advance for any help you can offer. JohnP Tag: Programmatically Halt Macro and Open VB Editor Tag: 968693
  - 6
    - Text with link I have date in cell a1 with dd-mmm-yy format. On the same sheet, I have another cell with "Allocation for " & A1 The result gives me Allocation for 39512 Required result is Allocation for 05-Mar-08 I dont understand why excel is unhappy with my format of dd-mmm-yy Any ideas? Regards, Madiya Tag: Programmatically Halt Macro and Open VB Editor Tag: 968691
  - 7
    - Include hyperlink in email message I have a macro that generates user files, populates shared directories with them, and I use a Ron de Bruin routine to send an Outlook message to inform each user of the file existence. I would prefer to send the user a hyperlink to the file, but my attempts just send plain text. Can anyone point me to sample code that does this, or does my question belong in the Outlook group? -- Al C Tag: Programmatically Halt Macro and Open VB Editor Tag: 968687
  - 8
    - Loop and append to different worksheets I posted this previously but I have discovered a few more things about it and still need some help. I need help making this macro loop through more than one worksheet. And I need to make sure that the information appends correctly. Or I need a different Macro that will do that. I have looked at http://www.contextures.com/excelfiles.html and unfortunately the macros there did not quite do what I need. I have anywhere from one to 16 different worksheets with no duplicate information. It is all in the same format, the worksheet names will differ from month to month. What I need to do is create new worksheets based on the information from a column. Column K could have anywhere from 1 to 6 different values A, B, C, D, E, or F. I need a seperate worksheet with all the rows containing the A's, all the B's, etc. This macro works great on one worksheet. I have tried a few things to make it work on more than one but I have had no luck and I know there are a lot of people here with much more experiance than I have. Thank you for any help you can provide. From a post by Bernie Deitrick: Sub ExportDatabaseToSeparateFiles() 'Export is based on the value in the desired column Dim myCell As Range Dim mySht As Worksheet Dim myName As String Dim myArea As Range Dim myShtName As String Dim KeyCol As Integer myShtName = ActiveSheet.Name KeyCol = InputBox("What column # within database to use as key?") Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(0, 0).Cells Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1) For Each myCell In myArea On Error GoTo NoSheet myName = Worksheets(myCell.Value).Name GoTo SheetExists: NoSheet: Set mySht = Worksheets.Add(before:=Worksheets(1)) mySht.Name = myCell.Value With myCell.CurrentRegion .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value .SpecialCells(xlCellTypeVisible).Copy _ mySht.Range("A1") mySht.Cells.EntireColumn.AutoFit .AutoFilter End With Resume SheetExists: Next myCell 'Optional section to export the sheets to separate files 'For Each mySht In ActiveWorkbook.Worksheets 'If mySht.Name = myShtName Then 'Exit Sub 'Else 'mySht.Move 'ActiveWorkbook.SaveAs "Workbook " & ActiveSheet.Name & ".xls" 'ActiveWorkbook.Close 'End If 'Next mySht End Sub Tag: Programmatically Halt Macro and Open VB Editor Tag: 968686
  - 9
    - charts source data series copying Hey all, I have a chart that is linked to a data page within an XL workbook. There is a main data page where I can have 5 or 6 column of data. There is another data page called "Map1 Data" where I can select two columns of data to compare. I then have a chart called "Map1" that gets updated "Chart_Activate" based on what columns are chosen. Once I am happy with the columns I am comparing is there a way to copy and paste the chart into another worksheet with just the data (and no lins to "Map1 Data" so that the chart no longer get updated if I change the columns that I am comparing in the "Map1 Data" worksheet? Thanks in advance, Lance Tag: Programmatically Halt Macro and Open VB Editor Tag: 968685
  - 10
    - Excel Macro works in 2000 but not in 2003 - HELP! All, I have a Macro that throws error 1004 on the ShowAllData method. This Macro will work on 2000 but not 2003. Below is the code. Any help would be greatly appreciated. I copied the start of the macro down to the error part. Does this have anything to do with ActiveSheet??? Sub Macro_Filter() ' Beginning here updates the BP340.RES sheet for BP340/RES Dim foundcell As Range Application.DisplayAlerts = False Sheets("BP340.RES").Activate ActiveSheet.UsedRange.Select FirstRow = Selection.Rows.Count SELECTROWS = "A3:N" & FirstRow Range(SELECTROWS).Select Selection.Delete Sheets("FY").Select If ActiveSheet.Protect Then ActiveSheet.Unprotect End If Range("A1").Select On Error GoTo ErrHndlr1 Selection.AutoFilter Field:=7, Criteria1:="BP340" Selection.AutoFilter Field:=8, Criteria1:="RES" ActiveSheet.UsedRange.Select LastRow = Selection.Rows.Count Selection.Range("A2:N" & LastRow).Select Selection.Copy Sheets("BP340.RES").Activate Range("A3").Select ActiveSheet.Paste ErrHndlr1: Sheets("BP340.RES").Activate Columns("G").Find("", after:=[G2]).Select Set CurrentSelection = Application.ActiveCell CurrentSelection.Value = "Grand Total:" With ActiveCell .Font.Bold = True .Font.Name = "Arial" .Font.Size = 10 End With Selection.EntireRow.Insert Set CurrentSelection = Application.ActiveCell CurrentSelection.Value = "BP340 Total:" With ActiveCell .Font.Bold = True .Font.Name = "Arial" .Font.Size = 10 End With Selection.EntireRow.Insert K = 3 Do Until Range("G" & K).Value = "" Range("G" & K).Select K = K + 1 Loop mystr = Trim(Str(K)) Range("J" & mystr + 1).Formula = "=sum(J3:J" & mystr & ")" TotSpent1 = Range("J" & mystr + 1).Value Range("J" & mystr + 2).Formula = "=sum(J2:J" & mystr & ")" Sheets("FY").Select Range("A1").Select If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData End If Tag: Programmatically Halt Macro and Open VB Editor Tag: 968682
  - 11
    - VBA Grouping Rectangle/Text Boxes Hey all, I am trying to label a couple of text boxes that are grouped together within a rectangle. ActiveChart.Shapes("Group 29").Select Selection.ShapeRange.Ungroup.Select ActiveChart.PlotArea.Select ActiveChart.Shapes("Group 28").Select Selection.ShapeRange.Ungroup.Select ActiveChart.PlotArea.Select ActiveChart.Shapes("Text Box 17").Select Selection.Characters.Text = Sheets(Idx + 1).Range("D16") ActiveChart.Shapes("Text Box 18").Select Selection.Characters.Text = Sheets(Idx + 1).Range("E16") Selection.ShapeRange.Regroup.Select ActiveChart.Shapes("Rectangle 13").Select Selection.ShapeRange.Regroup.Select Unfortunately, the GROUP ID changes each time I regroup so that what says "Group 29" now becomes "Group 30"? Any work around for this? Lance Tag: Programmatically Halt Macro and Open VB Editor Tag: 968679
  - 12
    - Offshore Data Entry Is The Only Way To Increase Your Business Offshore Data Entry provides data entry, data capture, dataentry services, large volume data processing and data conversion services through offshore facilities in India. Offshore data entry also provides form data entry, data capture, HTML/SGML coding, image scanning, file conversion with low cost, high quality,99.98% accuracy and time bound. Data Conversion Services offer cost effective data conversion projects, Outsourcing Data Conversion, advantages of data entry conversion services at offshore data entry in India. Outsourcing your data entry services to help in effectively managing your business by eliminating risks, reducing costs, and boost up your business growth, so you concentrate on your core business. Do visit us at http://www.offshoredataentry.com, to know more about our Offshore Data Entry Services and avail our services at affordable rates. E-mail Us: info@offshoredataentry.com Voice (India): +91-794-000 3000 Fax: +91-794-000 3002. Voice (USA): 1-402-210-2896, 1-402-210-2093 Fax (USA) : 1-775-205-3143. Tag: Programmatically Halt Macro and Open VB Editor Tag: 968676
  - 13
    - How can I do this.. I have a formula, ='G:\Salaries\[01a Salaries Apr07.xls]Data'!smithj, that pulls data from a separate workbook and works fine. But I would like to make this formula copyable across rows and columns. I have cells with the different dates (as text) across the top of my sheet and the different names (as text) down the side. Any suggestions? Thanks Richard Tag: Programmatically Halt Macro and Open VB Editor Tag: 968670
  - 14
    - error in exel when i am opening execl a tab appears that-"please wait while windows configurs Microsoft Office Professional Edition 2003"after that it says that "Installation Error File not found","original Installation Source Required:"I HA DTYER IN ALL WAY, PLEASE HELP ME i WANT MY EXCEL OFFICE, AND ALSO I WANT TO KNOW WHAT HAPPEND TO MY EXEL.. -- life is too short enjoy it... Tag: Programmatically Halt Macro and Open VB Editor Tag: 968665
  - 15
    - EXCEL Statement help Hello, can anyone help me please!!?? I have a cell named "% Compete" and columns named Week 1, Week 2, Week 3, Week 4 and so on up to week 28 When the user types in week 1, 50% and then 60% in week 2 I need the cell "% complete" to automatically update each time the user inputs the % complete. Please help!! Tag: Programmatically Halt Macro and Open VB Editor Tag: 968661
  - 16
    - Excel macros The questions are probably simple: 1 - can I create a macro that - after pointing (clicking, double clicking) to e.g. B5 carries the pointer to e.g. W23? 2 -I'd like macro "waiting" after ActiveSheet.Pictures.Insert( _ (something like "Browse"). My macro inserts always the same picture. Thank you for the hint... Tag: Programmatically Halt Macro and Open VB Editor Tag: 968657
  - 17
    - #NAME? on correct formula Hi, I want to enter a formula into a cell using VBA but while the formula is sound it leaves "#NAME?" in the cell until you manually click into and out of it. Is there anything I can do to stop this? ActiveCell.FormulaR1C1 = "=ROUND(VLOOKUP(R[3]C,'Part Time'!A:H,8,FALSE)*52/1.5,0)" Also, how do I fix a reference in R1C1 format? The formula below has apostraphes around the usual excel range but that needs to be fixed. any ideas? ActiveCell.FormulaR1C1 = "=IF(R[-4]C=""Full-time"",""N/A"",R[-2]C-SUMIF(B8:B5000,""Monday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part Time'!A:H,3)-SUMIF(B8:B5000,""Tuesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part Time'!A:H,4)-SUMIF(B8:B5000,""Wednesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part Time'!A:H,5)-SUMIF(B8:B5000,""Thursday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part Time'!A:H,6)-SUMIF(B8:B5000,""Friday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part Time'!A:H,7))" Thanks in advance for any help you can offer. JohnP Tag: Programmatically Halt Macro and Open VB Editor Tag: 968656
  - 18
    - one combobox affecting another Hi, I have 2 comboboxes ("Business" and "Name"). When Business is selected I would like Name to populate with all the names on the sheet that is named Business. I have tried the following code but get an error message saying that Name is an invalid qualifier. Can you change one comobox with another or is my code just flawed? Private Sub Business_Change() Sheets("" & Business).Select Range("E7").Activate While ActiveCell <> "" Name.AddItem ActiveCell.Value ActiveCell.Offset(0, 1).Activate Wend End Sub Thanks for any help you can offer. JohnP Tag: Programmatically Halt Macro and Open VB Editor Tag: 968655
  - 19
    - Test a cell for a comment presence from within a macro form 2007 I would like to completely control cell comments from a macro form. I am unable to determine if a cell does not have a comment or does have one. How do you relate the comment of each cell in a row of cells to its comment object. Comment index numbers do not seem to carry any relationship with them outside the object. Tag: Programmatically Halt Macro and Open VB Editor Tag: 968648
  - 20
    - Does VBA shortcut "or" statements? Thanks. *** Tag: Programmatically Halt Macro and Open VB Editor Tag: 968647
  - 21
    - OUT OF STACK SPACE ERROR I get an error everytime I run this. I need to find a way to avoid the error but still keep my program running. can anyone help me plz? Dim sngtime sub test() Range("A11").select Application.run "test.xls!sheet1.test" sngtime = timer + 1.5 do while timer < sngtime doevents loop application.run "test.xls!sheet1.test" end sub Tag: Programmatically Halt Macro and Open VB Editor Tag: 968644
  - 22
    - Need formula to calculate the average number of consecutive cells Hello! I have a column in Excel consisting of 1's and 0's. I need to calculate the average size of consecutive cells (groups of cells) containing 1's. For example, the average size of consecutive cells containing the number 1 in the following array is 3 (i.e., (2+4+3+3)/4 = 3): 1100111100000011100011100000 Can someone *please* suggest a formula to do this? Thanks in advance! Ilford Tag: Programmatically Halt Macro and Open VB Editor Tag: 968639
  - 23
    - Loop to copy rows down I need some sort of loop rRoutine which will copy the current row down 1 row at a time until it reaches row 4000 Tag: Programmatically Halt Macro and Open VB Editor Tag: 968636
  - 24
    - Splitting decimals I need to split a figure with a decimal value of 2 places, into 2 seperate cells. EG I have a figure of 1.56 in cell a1 and I want to split it into A2 and A3 A2 will hold the number before the decimal point and A3 will hold the number after the decimal point. Thanks Tag: Programmatically Halt Macro and Open VB Editor Tag: 968633
  - 25
    - convert second word in range to Proper when first variable length - Hi - thanks for having a look I have 1 column of names: D (in a many column worksheet) with initials and surname in upper case e.g. AB PATTERSON or A PATTERSON and I want the surname only (not initials) as proper. Also there is the case of two people e.g. AB PATTERSON & B BANJO and hyphenated names - e.g. AB PATTERSON-POET How can I have a function or VBA asertain where the surname/s start and convert. Hope someone can help Cheers Peta Tag: Programmatically Halt Macro and Open VB Editor Tag: 968627
- Next
  - 1
    - select the range with the cell's position is a variable. can anyone tell me how to select a range which will keep changing? for eg: Sub select_range() Dim abc As Range Dim a As Integer Dim b As Integer a = 2 + 2 b = 2 abc = Range("ab") abc.Select End Sub How to modify the code if i wish to make the a, b as a variable? Tag: Programmatically Halt Macro and Open VB Editor Tag: 968623
  - 2
    - DECLARE VARIABLES PROBLEM HI ALL I have this macro whitch copy antire row from one wbook to another If a value is >=x ;it works with 1 workbook : PART 1 OF TOPIC ! Option Explicit Sub Copy_Ranges() Dim FromWks As Worksheet Dim DestWks As Worksheet Dim NextRow As Long Dim myCell As Range Dim myRng As Range Set FromWks = Workbooks("YTA1.xls").Worksheets("sheet1") Set DestWks = Workbooks("R1.xls").Worksheets("sheet1") With FromWks Set myRng = .Range("BD91", .Cells(.Rows.Count, "BD").End(xlUp)) End With For Each myCell In myRng.Cells If myCell.Value <= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy _ Destination:=.Cells(NextRow, "A") End With End If Next myCell End Sub PART 2 OF TOPIC . I change this macro for loop in 3 steps ; the macro becomed so : Option Explicit Sub Copy_Ranges() Dim FromWks As Worksheet Dim DestWks As Worksheet Dim NextRow As Long Dim myCell As Range Dim myRng1 As Range Dim myRng2 As Range Dim myRng3 As Range Set FromWks = Workbooks("Registru1.xls").Worksheets("1") Set DestWks = Workbooks("R1.xls").Worksheets("1") With FromWks Set myRng1 = .Range("BD91:BD22000") End With With FromWks Set myRng2 = .Range("BD22001:BD44000") End With With FromWks Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value >=33 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .cells(nextrow,"A").pastespecial paste:=xlpastevalues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select Selection.AutoFill Destination:=Range("B29001:BB36000"), Type:=xlFillDefault Range("B36001:B44000").Select Selection.AutoFill Destination:=Range("B36001:BB44000"), Type:=xlFillDefault For Each myCell In myRng2.Cells If myCell.Value >=33 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .cells(nextrow,"A").pastespecial paste:=xlpastevalues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents Sheets("1").Select Range("B44001:B51000").Select Selection.AutoFill Destination:=Range("B44001:BB51000"), Type:=xlFillDefault Range("B51001:B58000").Select Selection.AutoFill Destination:=Range("B51001:BB58000"), Type:=xlFillDefault Range("B58001:B65536").Select Selection.AutoFill Destination:=Range("B58001:BB65536"), Type:=xlFillDefault For Each myCell In myRng3.Cells If myCell.Value >=33 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .cells(nextrow,"A").pastespecial paste:=xlpastevalues End With End If Next myCell End Sub PART 3 OF TOPIC . For open 3 workbook this macro and work with them , I ' ve done this changes : Option Explicit Sub Copy_Ranges() Dim FromWks1 As Worksheet Dim FromWks2 As Worksheet Dim FromWks3 As Worksheet Dim DestWks As Worksheet Dim NextRow As Long Dim myCell As Range Dim myRng1 As Range Dim myRng2 As Range Dim myRng3 As Range Workbooks.Open ("D:\WAVE\YTA1.xls") Set FromWks1 = Workbooks("YTA1.xls").Worksheets("1") Set DestWks = Workbooks("R1.xls").Worksheets("1") With FromWks1 Set myRng1 = .Range("BD91:BD22000") End With With FromWks1 Set myRng2 = .Range("BD22001:BD44000") End With With FromWks1 Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value >= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select Selection.AutoFill Destination:=Range("B29001:BB36000"), Type:=xlFillDefault Range("B36001:B44000").Select Selection.AutoFill Destination:=Range("B36001:BB44000"), Type:=xlFillDefault For Each myCell In myRng2.Cells If myCell.Value >= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents Sheets("1").Select Range("B44001:B51000").Select Selection.AutoFill Destination:=Range("B44001:BB51000"), Type:=xlFillDefault Range("B51001:B58000").Select Selection.AutoFill Destination:=Range("B51001:BB58000"), Type:=xlFillDefault Range("B58001:B65536").Select Selection.AutoFill Destination:=Range("B58001:BB65536"), Type:=xlFillDefault For Each myCell In myRng3.Cells If myCell.Value >= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C44001:BB65536").Select Selection.ClearContents Workbooks("YTA1.xls").Close SaveChanges:=False Workbooks.Open ("D:\WAVE\YTA2.xls") Set FromWks2 = Workbooks("YTA2.xls").Worksheets("1") With FromWks2 Set myRng1 = .Range("BD91:BD22000") End With With FromWks2 Set myRng2 = .Range("BD22001:BD44000") End With With FromWks2 Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value >= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select Selection.AutoFill Destination:=Range("B29001:BB36000"), Type:=xlFillDefault Range("B36001:B44000").Select Selection.AutoFill Destination:=Range("B36001:BB44000"), Type:=xlFillDefault For Each myCell In myRng2.Cells If myCell.Value >= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents Sheets("1").Select Range("B44001:B51000").Select Selection.AutoFill Destination:=Range("B44001:BB51000"), Type:=xlFillDefault Range("B51001:B58000").Select Selection.AutoFill Destination:=Range("B51001:BB58000"), Type:=xlFillDefault Range("B58001:B65536").Select Selection.AutoFill Destination:=Range("B58001:BB65536"), Type:=xlFillDefault For Each myCell In myRng3.Cells If myCell.Value >= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C44001:BB65536").Select Selection.ClearContents Workbooks("YTA2.xls").Close SaveChanges:=False Workbooks.Open ("D:\WAVE\YTA3.xls") Set FromWks3 = Workbooks("YTA3.xls").Worksheets("1") With FromWks3 Set myRng1 = .Range("BD91:BD22000") End With With FromWks3 Set myRng2 = .Range("BD22001:BD44000") End With With FromWks3 Set myRng3 = .Range("BD44001:BD65536") End With Sheets("1").Select Range("B91:B7000").Select Selection.AutoFill Destination:=Range("B91:BB7000"), Type:=xlFillDefault Range("B7001:B14000").Select Selection.AutoFill Destination:=Range("B7001:BB14000"), Type:=xlFillDefault Range("B14001:B22000").Select Selection.AutoFill Destination:=Range("B14001:BB22000"), Type:=xlFillDefault For Each myCell In myRng1.Cells If myCell.Value >= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C91:BB22005").Select Selection.ClearContents Sheets("1").Select Range("B22001:B29000").Select Selection.AutoFill Destination:=Range("B22001:BB29000"), Type:=xlFillDefault Range("B29001:B36000").Select Selection.AutoFill Destination:=Range("B29001:BB36000"), Type:=xlFillDefault Range("B36001:B44000").Select Selection.AutoFill Destination:=Range("B36001:BB44000"), Type:=xlFillDefault For Each myCell In myRng2.Cells If myCell.Value >= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C22001:BB44005").Select Selection.ClearContents Sheets("1").Select Range("B44001:B51000").Select Selection.AutoFill Destination:=Range("B44001:BB51000"), Type:=xlFillDefault Range("B51001:B58000").Select Selection.AutoFill Destination:=Range("B51001:BB58000"), Type:=xlFillDefault Range("B58001:B65536").Select Selection.AutoFill Destination:=Range("B58001:BB65536"), Type:=xlFillDefault For Each myCell In myRng3.Cells If myCell.Value >= 32 Then With DestWks NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1 myCell.EntireRow.Copy .Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues End With End If Next myCell Application.CutCopyMode = False Sheets("1").Select Range("C44001:BB65536").Select Selection.ClearContents Workbooks("YTA3.xls").Close SaveChanges:=False End Sub PART 4 OF TOPIC . I want to work with 10 workbooks in one macro . Name of this workbooks is from YTA1 to YTA10 . MY PROBLEM IS : HOW CAN I declare the variables for all workbooks ?THE ONLY PROBLEM IS TO DECLARE VARIABLES IN THIS MACRO ! Please don't impresionate for the big size of this macro , only problem is to declare variables for the 10 workbooks and them Rng1 , Rng2 and Rng3 ! ! Something I write wrong , because after first workbook the macro don't work with speed !! It copy entire row in the other workbook very slow ! (ps:I have 231 workbooks , named from YTA1 to YTA231, but I think it cann't be posible to work with all them in one only macro [ a macro cann't be bigger of 64 kb ! ]) Any sugestion will be wellkome ! Many thanks Tag: Programmatically Halt Macro and Open VB Editor Tag: 968611
  - 3
    - F11 Crashes Excel In the spreadsheet I'm developing the F11 key is causing concern, especially if pressed. (Or, when someone presses it...!) You either get (after Excel struggles a bit) - Excel cannot complete this task with available resources. Choose less data or close other applications. Or the much worse - Microsoft Excel has encountered a problem and needs to close. We are sorry for the inconvenience. This happens on every computer (tested = 4), not just mine. I've no idea what F11 is meant to do - but can it be disabled? Thanks - Kirk Tag: Programmatically Halt Macro and Open VB Editor Tag: 968610
  - 4
    - Two Open Workbooks Hi, I'm not sure I'm doing this right. I used this to open the other workbook in another procedure and it worked fine. Now I'm getting an out of range message. Also, am I identifying the two different workbook correctly in determining the LusedRow? Dim wbkCopyFrom As Workbook Set wbkCopyFrom = Workbooks("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo Done If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''")) Application.ScreenUpdating = False 'get the last tenant's row in the From workbook FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row 'get the last tenant's row in the New workbook NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row -- Thanks for your help. Karen53 Tag: Programmatically Halt Macro and Open VB Editor Tag: 968608
  - 5
    - Linked Cells and Sort Issues Hi All. I've created a workbook where a group of cells from one worksheet (worksheet B) are populated by way of a link to another worksheet (worksheet A). Worksheet B also has manual input in the column next to the columns that are linked from worksheet A. When worksheet A gets sorted the manual input in worksheet B doesn't sort with the other fileds. How can I make them stay together? Tag: Programmatically Halt Macro and Open VB Editor Tag: 968606
  - 6
    - RunTime error 462: Remote Server not avaliable??? With Excel and Word 2003, I'm using Excel VBA to automate a Word document. Everything is going pretty much okay so far - except I keep getting a Runtime 462 error - Remote server not available. First, all this is on my own machine - there are no remote servers involved. Second, I did do some searching and found that this can crop up when an object - like "range" - is not specifically declared as belonging to either Word or Excel. So VB will choose, and may choose incorrectly. So I went back and made sure everything involved here was declared to an application. Immediately before the error, I set a Word range and an Excel range, and copy the Excel rnage into the Word doc as a table. That works fine. Then I set an object to the table in Word and try to manipulate it. That's when it errors. If anyone can help me out. I would appreciate it. Ed Set wdRng = doc.Bookmarks("XL1").Range wdRng.MoveEnd wdParagraph, 1 wdRng.Collapse wdCollapseEnd Set rng = wks.Range("A2:H30") rng.Copy wdRng.InsertBefore Chr(13) wdRng.Collapse wdCollapseEnd wdRng.Paste Set tbl = wdRng.Tables(1) ' NOTE: tbl is Dim'd as Word.Table With tbl .TopPadding = InchesToPoints(0.01) '***** ' When stepping through with F8, it errors when I get ' to the .TopPadding line .BottomPadding = InchesToPoints(0.01) .LeftPadding = InchesToPoints(0.02) .RightPadding = InchesToPoints(0.02) .Spacing = 0 .AllowPageBreaks = True .AllowAutoFit = False .Rows.Alignment = wdAlignRowCenter .Rows.AllowBreakAcrossPages = False .Range.Cells.VerticalAlignment = wdCellAlignVerticalCenter .Rows.SpaceBetweenColumns = InchesToPoints(0.04) .Rows.Height = wdRowHeightAuto End With Tag: Programmatically Halt Macro and Open VB Editor Tag: 968604
  - 7
    - Copy & Paste "Equal" Function: Adding Distance I'm hoping someone can help me with the following: 1). In (sheet 2, cell A1) I use the Equal formula to make it "= sheet 1, cell A1" 2). Still in Sheet 2: I copy & paste (cell A1) into (cell A2). Of course, my result will be "= sheet 1, cell A2) as the formula will maintain my original distance. *However, I need the result to be "= sheet 1, cell A6." How can I do this - get it to "skip" cells so to speak? Any Help will be most appreciated and save me a considerable amount of time in hard keying formulas in. Rob Tag: Programmatically Halt Macro and Open VB Editor Tag: 968596
  - 8
    - Query SQL Server On Entry Please tell me if this is possible... A cell receives an ID number entered by the user. Upon update of the cell, I'd like to query a view in a SQL server database. If the entered ID exists, a value (say "X") is placed in another cell, else, a different value (say "Y") is placed in another cell. Where should I start when developing interactive MSSQL queries from Excel? MS Excel 2002 SP3. Thanks in advance. Tag: Programmatically Halt Macro and Open VB Editor Tag: 968593
  - 9
    - Excel macro to open Notepad and save with Date/Time in filename I've tried to create this macro using the Shell command which works for opening Notepad but I can't get the part about putting the date and time into the filename of the Notepad file. The date and time format could have blanks between such as mm dd yyyy hh mm ss but if it has underscore or other characters that is ok. Tag: Programmatically Halt Macro and Open VB Editor Tag: 968592
  - 10
    - AutoFilter... not quite right I have a Inputbox i would like to use to set the AutoFilter criteria1. Then display the results of that filter on the active worksheet. This is what i have so far... Sub Sort() Dim Message, Title, MyValue Message = "Please Enter A Date" Title = "Title" MyValue = InputBox(Message, Title) Range("Headers").Select Selection.AutoFilter Selection.AutoFilter Field:=5, Criteria1:=MyValue End Sub The code almost works, it select the headers, creates the auto filter but dosent display the results of the filter. the ultimate goal here is to only see the date entered on the active sheet to be printed. Any help would be great. Tag: Programmatically Halt Macro and Open VB Editor Tag: 968589
  - 11
    - Sort blocks of data as if each block was a single record Hi all, not sure if this is possible or not. beginning on row 6 I have several blocks of data. Each block is exactly 9 rows long, 40 columns wide. In the first row of each block(and only the first row of each block), column A, there is an identifier. I would like to sort based on that identifier, but each block has to stay in tact. So essentially, each block of 9 rows must move together as if it were a single row of data. Thanks for all of your help!! Tag: Programmatically Halt Macro and Open VB Editor Tag: 968588
  - 12
    - comment or input message I need to show a comment or data validation input message when a user clicks in a cell. Comments won't work because we don't want the indicator to show. Data validation won't work because it won't allow me to publish a List (Excel 2003) to a SharePoint server when data validation is present. So, I'm using the below code. However, I don't want an actual message box to appear that a user must click OK to close. Is there a way, in code, to show a cell comment momentarily when a cell is selected? Thanks. If Not Intersect(Target, Range("D2:D25")) Is Nothing Then MsgBox ("Please enter name.") End If Tag: Programmatically Halt Macro and Open VB Editor Tag: 968587
  - 13
    - Avoid saving file with code in Hi, using Excel 2000. I want to ensure that a file with the code isn't saved in error or during testing. The code below saves the active workbook which works the majority of times but has on occasion, saved the file with the code. I have set up a line of code to capture the active file name when the code is run but can't get the code that saves the file to display a message if the file bring saved is the one with the code and then closes the file without saving. Thanks in advance of any help. Rob ' this line sets iFname to the active file name iFname = Application.ActiveWorkbook.Name 'save the active file Set Wb = ActiveWorkbook Application.DisplayAlerts = False Wb.Save Application.DisplayAlerts = True Tag: Programmatically Halt Macro and Open VB Editor Tag: 968582
  - 14
    - Printer Hello, I would need to know how to print to a specific printer and then set the default printer back to the original printer. Thank you, QB Tag: Programmatically Halt Macro and Open VB Editor Tag: 968567
  - 15
    - Seaching a Word Document from Excel Part ii I have gottern some great help from Helmut Weber, MVP WordVBA on this subject but have run into another problem. At the end I will put what I'm trying to do. When I try to search the word documents I have found that doing a search using the following code cause a problem with the varible rDcm due to the fact that the words I am search for are in a textbox in the word document and to make manners worst there is a border going around the outside of the docuemnts (they are certificates). Sub Test455() Dim col As Integer, row As Integer, s_counter As Integer Dim sPth As String Dim sNam As String Dim oWrd As Object col = 1 row = 2 s_counter = 1 sPth = "C:\Test Folder\" Set oWrd = CreateObject("Word.Application") oWrd.Visible = True Sheets("Sheet3").Select sNam = Dir(sPth & "*.doc") While sNam <> "" Range("C1").Value = s_counter oWrd.Documents.Open sPth & sNam Dim rDcm As Object Set rDcm = oWrd.ActiveDocument.Range Debug.Print rDcm With rDcm.Find .ClearFormatting .Text = Range("E3").Value While .Execute rDcm.Select Range("A" & row).Value = sNam row = row + 1 Wend End With s_counter = s_counter + 1 oWrd.ActiveDocument.Close sNam = Dir Wend oWrd.Quit Set oWrd = Nothing End Sub Besdies this same, which Helmut provided I have tried to use the Built-in Dialog Box functions such as wdDialogEditFind but these seem to have another problem where I can't get it return a value based on the fact that it found the word. What I am trying to do is to create a list of file names of Word Documents (certificates) that have been issues that have certain words in them. These words will come from a list on a Excel Spreadsheet. Ideally I would like tocreate something similar to the Search for words or pharse in Windows Search menu item. The sample code above will do the search but as I said it seems to ahve a problem with the fact that there is this border around a textbox and the only results returned are something that looks like a small "r" Sorry for the length of this posting and thanks in advance for any help. Tag: Programmatically Halt Macro and Open VB Editor Tag: 968563
  - 16
    - copy data from a list I wish to extract data from a range that meets certain critera and place it on another part of my spreadsheet, I am using a simple filter and copy and paste, was wondering if I could do it with out activated the sheet, heres what i am using at the mo CST = Range("D7") Application.ScreenUpdating = False Workbooks.Open Filename:= _ "http://10.234.95.121/Complex_Job_Lists/Wlr3/PreCalling.csv" Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:=CST Range("A2:l54000").Select Selection.Copy Windows("end user calling list ver8.xls").Activate Sheets("database").Select Range("B15").Select ActiveSheet.Paste Range("B4").Select Range("A15:A1000").ClearContents Range("A14").Select Tag: Programmatically Halt Macro and Open VB Editor Tag: 968559
  - 17
    - how can a macro be paused for input and then resume executio In the following macro in excel please answer the italic question Range("A1").Select Selection.End(xlDown).Select Selection.End(xlDown).Select ActiveCell.Range("A1:G1").Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Select Dim x As String x = InputBox("Put Value", "abc") ' How can the macro be paused here and after input what key will resume it ? ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.Select End Sub Thanks for your help Tag: Programmatically Halt Macro and Open VB Editor Tag: 968554
  - 18
    - InputBox Help Hi All, I'm sure this is an easy question for most, except for me! I have an InputBox that gets a value and then copies the number down. Numbers will already be in the column from the prior week, and could be used again. However, if the user invokes the macro by mistake and then clicks "Cancel" the first entry gets wiped out. I'm pretty sure I could get around this, but I really want the correct way of doing this. Enclosed is my ratty code; help would be much appreciated! Sub InputIncTon() Sheets("Credits").Select Range("G4").Select ActiveCell = InputBox("What is the INC/TON?", "INC/TON") If ActiveCell = "" Then GoTo GetMeOuttaHere With Worksheets("Credits") .Range("G4:G600").FillDown End With GetMeOuttaHere: End Sub Tag: Programmatically Halt Macro and Open VB Editor Tag: 968547
  - 19
    - Creating separate spreadsheets from a master spreadsheet I would like to know if there is a way to create individual spreadsheets from a master spreadsheet thru a macro.? For example, my master spreadsheet has 10 salesmen listed and I would like to have a spreadsheet for each salesman. Thanks. -- Bob Tag: Programmatically Halt Macro and Open VB Editor Tag: 968544
  - 20
    - how do i set number in cell to add 1 upon opening sheet I am setting up a workorder sheet that will be printed and saved. I would like the workorders to have numbers automatically - the number in the cell would increase by 1 each time I opened the master sheet. Is this a macro, a button? I have Excell 2003. Tag: Programmatically Halt Macro and Open VB Editor Tag: 968543
  - 21
    - Use count data in formula I would like to enter a formula in column J that refers to a variable range in column a. I think I'm going about it in the wrong way. Everytime you run the macro, it updates data from an XML file and creates a new worksheet with today's date. Can you help this beginner? Dim numrowsdata As Integer Dim ws As String numrowsdata = Selection.Rows.Count ws = ActiveSheet.Name Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range("j7").FormulaArray = _ "=SUM(('" & ws & "'!R2C8:R" & numrowsdata & "C8=RC[-1])*('" & ws & "'!R2C6:R" & numrowsdata & "C6))" Tag: Programmatically Halt Macro and Open VB Editor Tag: 968534
  - 22
    - Area Chart not refreshing/updating properly Excel 2002, SP3 I am actually using 2 charts to make up the apparence of 1 chart. The first chart is the area chart so as I can get the goal line to go across the entire plot area. (This is the chart that I'm having issues with) The second chart is a line chart, which as an object, is over the top of the area chart. Both charts are setup as interpolated, so as lines are drawn from data point to data point which other fields either being left as blank or having the formula of "=NA()" are skipped on over as far as line drawing on the charts is concerned. Data Source of the area chart is setup as "Charts!B37:P37" (If I put in the "Q" in place of the "P", line wouldn't go all the way to the right side of the plot area) Data Source of the line chart is setup as "Charts!B39:Q39" and "Charts!B41:Q41" The workbook (among others) is set to "Manual Calculation", which even when using Shift-F9 manually, or using one of the following 2 VBA codes, the chart does not update as would be expected: Note, the m_chtProductionRateAreaChart is the ChartObject m_chtProductionRateAreaChart.Chart.Refresh Or m_wshOperatorCharts.Calculate 'The worksheet the chart and data are on. One more thing I have noticed. It seems after the VBA code is done with it all, the Line Chart is selected, and I'm not sure if this is causing a problem with the area chart or not. If you will, for rows 37 through 41, each row is it's own row, no 2 rows are merged together, but every 2 columns between B and S are merged together so as to be able to have the data on the worksheet centered between the grid lines on the chart while allowing for both 1st shift and 3rd shift data for those same days showing side by side below on the worksheet. Yes, this means data points are shown a bit to the left side rather than centered on the line chart, but that will be dealt with later as that's just a cosmetic issue, and I already have an idea how to resolve that issue This goal area chart not adjusting properly is more of an issue cause it's gives off the wrong perception by far. The only way I so far have been able to find to get the chart to update properly is to manually adjust the Y axis setting to either include or exclude the tick label. However, by doing that, it would also mean that I would have to reset the Left and Width properties of the PlotArea on the chart. I'm already having to set these settings within VBA anyhow on my chart objects so as they print out properly. The code is adjusting the MaximumScale and MajorUnit properties on the Value Axis properly as expected, just not refreshing the graph itself, which would be the values. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 Tag: Programmatically Halt Macro and Open VB Editor Tag: 968533
  - 23
    - Listbox Max Columns EXCEL 2003 I am filling 2 listboxes. The first listbox is filled like this: lr = LastRow(ThisWorkbook.Worksheets("Mesh")) With ThisWorkbook.Worksheets("Mesh") Set MeshInventory = .Range(.Cells(2, "A"), .Cells(lr, "K")) End With With Me.lbMeshInventory .ColumnHeads = False 'can't use this feature here .ColumnCount = MeshInventory.Columns.Count .List = MeshInventory.Value End With Since this source is bound, it lest me have more than 10 columns in the listbox. When the user double clicks an item on the first Listbox, I want that item added to the second listbox. I'm pulling my hair out trying to figure out how to make the second listbox bound so it can also have more than 10 columns. Dave Peterson posted something on 3/1/07 and said to use .AddItem, but I get an error when the code gets to the 10th column. thank you, Steve Tag: Programmatically Halt Macro and Open VB Editor Tag: 968532
  - 24
    - Getting Picture Into Image In UserForm I am guessing that this is incredibly easy, but it is beyond me. All I want to do is click the Image box on the UserForm and be able to select a Picture. I can very easily get the code to write to a desired cell using xlDialogInsertPicture, which I like because it ensure that it is an appropriate object. I can't get the image to show up on the UserForm using: Image1.Picture=LoadPicture(Sheet("PictureSheet").Cells(1,1).Value) I can imaging a lot of ways to do this, but a good top level method would be best. In support of my own ideas: - Can I get the path from xlDialogInsertPicture? - Is there a generic pictures filter for GetOpenFilename? - Can I extract the image from the UserForm and insert it somewhere else later or is it lost? Any help is appreciated. Thanks in advance Tag: Programmatically Halt Macro and Open VB Editor Tag: 968529
  - 25
    - Excel 2007 Oracle DB Help Heres the breakdown. I have an oracle db named MXFP and the server is named MXFP. I use a msquery to pull data from the GLaccounts table. which returns 1131 rows of data. I need to build something to retreive all information from another table named workorders for each of these GLaccount rows. IE: SELECT * FROM WORKORDERS WHERE GLACCOUNT=A1 A1 = Cell a1 of the GLaccounts sheet. Not sure how i can go about this. Ive tried using sql.request but i cant build the friggin script properly and i cant find XLODBC.XLA add- in for 2007. Any help? Tag: Programmatically Halt Macro and Open VB Editor Tag: 968527

I have an IF statement in a macro. If one branch is selected, I would
like to halt (not end) macro execution and open up the VB editor
window. What code would I use to accomplish this?...TIA, Ron

Top

Re: Programmatically Halt Macro and Open VB Editor by Bob

Bob
Fri Mar 14 10:17:39 CDT 2008

Insert

Stop

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ron" <oitbso@yahoo.com> wrote in message
news:d4fe9f04-3cec-4247-a8e3-d66c109b3c1c@n36g2000hse.googlegroups.com...
>I have an IF statement in a macro. If one branch is selected, I would
> like to halt (not end) macro execution and open up the VB editor
> window. What code would I use to accomplish this?...TIA, Ron

Top

Re: Programmatically Halt Macro and Open VB Editor by ron

ron
Fri Mar 14 10:34:18 CDT 2008

On Mar 14, 9:17=A0am, "Bob Phillips" <bob....@somewhere.com> wrote:
> Insert
>
> Stop
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy=
)
>
> "ron" <oit...@yahoo.com> wrote in message
>
> news:d4fe9f04-3cec-4247-a8e3-d66c109b3c1c@n36g2000hse.googlegroups.com...
>
>
>
> >I have an IF statement in a macro. =A0If one branch is selected, I would
> > like to halt (not end) macro execution and open up the VB editor
> > window. =A0What code would I use to accomplish this?...TIA, Ron- Hide qu=
oted text -
>
> - Show quoted text -

Thanks Bob!

Top