Posts by MrkFrrl

    Re: Optimizing VBA Speed


    If what you've got (or had) works, stick with it.


    But I think what he meant would be something like this:


    Code
    For i = 1 To 100 
    Sheets("Sheet1").Select
            rndRow = Int(Selection.CurrentRegion.Rows.count * Rnd + 1) 
            lastRow = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1 
            Sheets("Sheet1").Rows(rndRow).EntireRow.Copy _
            Sheets("Sheet2").Range("A" & lastRow)
    
    
        Next i



    Quote from jonny

    How can I change the code that the rows will cut and not copy.
    I changed the row in a code from

    Code
    Selection.Copy


    to

    Code
    Selection.Cut


    but it leaves me blank rows.

    Re: Change Images With ComboBox


    Public module. ShowFilterForm macro is invoked by a form button:


    Private module linked to created UserForm. Drop-down box is on this UserForm. First button below is for OK, obviously; second, Cancel. Other initialize macro would be within the same private userform module:



    Hope this helps. Should give you a good idea where you need to start, at least.


    Quote from corkyo4

    Is that copied into the module section?? and also how does it link to the same cell that the combo box is linked to??

    Re: Change Images With Combo Box


    Well, then, you need a private sub that initializes when the workbook opens. With the name of the combo box, in this case CancelCode, you'd have various items in it:

    Code
    Private Sub UserForm_Initialize()
    With CancelCode
    .AddItem "Choice1"
    .AddItem "Choice2"
    .AddItem "Choice3"
    End With
    ' Shows second selection by default; 0 is first
    CancelCode.ListIndex = 1
    End Sub


    Quote from corkyo4

    That is a good method, but unfortunately it doesnt really suit my needs


    sorry

    Re: Scrampled Screen After Vba Executes


    It sounds like you've got a BeforeSave event in the Workbook module being triggered. Is this the case? If so, can you do away with it, or make it a conditional case?


    I don't know what would be causing the temporary image of which you speak. If scrolling takes care of it, would putting something like this right before the application.screenupdate = true work?

    Code
    ActiveWindow.SmallScroll Down:=50
        ActiveWindow.ScrollRow = 1


    Would something like this work?

    Code
    ActiveWorkbook.RefreshAll


    Re: Extract Middle Text


    Try this:




    Quote from emrena

    I want to write a macro that is able to copy the 5th position to the 10th position characters from one cell to another. For example, in Cells(B1) contains: Mon Jan 8 09:00:02 2007. Then I want to copy "Jan 8" to Cells(A1). How do I do that? Also, i am not supposed to hardcode the cells. The codes should look something like that


    start = [ib]*[/ib]....
    end = InputBox...


    [vba]For i = start to end
    Cells(i,"A").Value = (whatever function to copy 5th to 10th char of Cells(i,"B")).......
    Next i[/vba]

    Re: Macro To Run And Then Delete Itself?


    Quote from Code Numpty

    Thanks Roy, do users of my files also have to check Trust access to Visual Basic Editor for it to work, or is this just for me to be able to put it in at the end of my macro?


    Would recommend a digital signature before allowing them to set security to low, as other threats/problems could then be allowed; but try to make a macro that copies the workbook without macros first.

    Re: Macro To Delete Itself


    Instead of trying to delete the macro when it's done, can you simply make a macro to make a copy of the workbook without macros? Then you distribute the copies to the folks who you don't want to be bothered with the macro-message, or have them access it in a shared location.


    Quote from Code Numpty

    Run on another machine resulted in
    Run-time error '1004' Programmatic access to Visual Basic Project is not trusted.
    I'll get them to check Trust access to Visual Basic Editor to ascertain that this is the problem. Only 3 people will use this template so if it works with this change I'll get them to make it.

    Re: Exit Cell Event


    Haven't followed this entire thread. Would something like this work?


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim CellName$
    CellName = ActiveCell.Address
    ' be sure to use the dollar-signs when referencing the cell names below
    If CellName = "$B$1" or CellName = "$A$1" Then 
    MsgBox "test" ' your code here
    end if
    End Sub


    Quote from max_lux

    I am running the following code to check if a value = 0 is in specific cells. Because this code on all the cells runs each time any cell in the workbook is calculated, the screen flashes. I would like to stop this screen flashing by having the check done only when the specific cells are exited.


    Thanks, in advance!


    Re: Create Workbook With Macro Code Within


    If you're simply wanting to have a worksheet event fire on a newly created sheet, the simplest way to do this would be, instead of creating a brand new worksheet, copy one that already has the code intact--for example,

    Code
    Sheets("Sheet1").Copy Before:=Sheets(1) ' where Sheet1 would have the code you need


    Quote from Blibo

    Okay
    Right now I use a VBA-Code to create new worksheets in a workbook.


    my Question:
    is it possible to attach code to the newly created Worksheet via vba-code??


    Thanx for help...

    Re: Launching Copernic Desktop Search


    Never heard of it, but why not use Excel's Find or GoTo in a macro instead of this tool you mention?


    Quote from sengkangboy

    Did anyone use the above tool ? Can we use a macro to launch that tool to search for words in a excel cell ?

    Re: Convert To Word


    If you wanted the data from Excel refreshed automatically in Word when opened, one way to do this would be to -


    Open the Insert menu in Word, then select Object from the list.


    Click on the Create from File tab,
    click the Browse button,
    navigate to the object (i.e., the file you want auto-refreshed),
    click in the box Link to File,
    and then click on OK.


    Then, when you open Word again, if the data has changed in Excel and has been saved, it should reflect the new data (providing the person who opens it has access to the directory in which it's linked). I know that this works, but don't know if this would work with more than one spreadsheet, however.

    Re: Creating A Macro To Input Reptetive Text In Frot Of New Input


    Sure, it can be done. The easiest way would be with an InputBox. Check VBA's help-section for further info. I just thought that this method would be quickest, with you inputting the numbers necessary and then running the macro to complete the area.


    Quote from [email protected]

    Thanks for the quick reply, it works, but what I have in mind is that when I execute the macro it will enter the text "TAX0018" and then I will enter the other four digits and hit enter. I will repeat the macro until I finish with the input. I use to do this with the old Excel macros before visual basic. The macro will promt me to enter the input. Can this be done with the visual basic macros?

    Re: Web Query Changing URL


    How would it possibly be able to guess what they change their URL to? Even you don't know, and you're the one writing the macro.


    Maybe, if the one page always kept the links in the same place, and you queried that page and returned the link as a string???


    They probably change their URL daily just so that doesn't happen.


    Re: Open Sheet 2 When Closing Sheet 1


    If you simultaneously press ALT-F11, you'll open up Excel's VBA environment.


    Now, if you look to the left, you'll see a few different items representing the sheets and the workbook.


    Double-click on the one that represents the worksheet where you'd want the cell change situation to apply.


    You'll see a drop-down menu that says General. Change that to Worksheet.


    Now, to the right of that, you'll see various events that you can have the worksheet event trigger. You'd want to use the selection change event and then put your code within the corresponding macro.


    I don't get paid for this--and wouldn't accept pay even if asked--so I'm not doing all your homework and won't go further.


    You need to put a little effort on your part if you want this to work.


    Quote from Excel Noob

    Hi I am trying to figure out if there is a way to have a message box open asking if I want to open Sheet 2 if there are any values entered into a range of cells on sheet 1. The range of cells are named Used. So if there are any values entered into the Used range then upon closing the workbook I would like a message box to appear asking if the user would like to open Sheet 2. If there is a way to put buttons in that box that says OK and Close or Yes and No that would be good. If the user clicks on OK or Yes then Sheet 2 would open. If they click on Close or no then the workbook would just close. If it would be easier to just to have sheet 2 open upon sheet 1 closing then that would be fine as well. Thanks in advance for your help. If anybody wants to see the workbooK that this pertains to it is posted in the thread titled "Update Cells on Opening" posted by me. Sheet 1 is titled VS Count and Sheet 2 is titled VSRequest.

    Re: Creating A Macro To Input Reptetive Text In Frot Of New Input



    Quote from [email protected]

    I have about 800 inputs of 11 character long of which the first 7 characters are the same for every one and the last four character are different. How do I make a macro that input the first 7 character and I input the other four.

    Re: Private Sub Keeps Looping


    I really have no idea offhand why it would be looping, but sometimes I've noticed weird things occur from Private Subs.


    Can you have the Private Sub call a "public" Sub in a standard module, which would then have the rest of your code in it--like this?


    See if that makes a difference.


    Code
    Private Sub cmbDI_GrowthBasis_Change()
    Call NilChecker
    end sub


    In the standard module:


    Quote from TonyJN

    I have a piece of code attached to a combo box. When selected the code loops through 3, even 4 times for no apparent reason. Has anyone come across this before? The loop can start either after the final "end sub" or part way through (.clearcontents mainly)


    Have included the code just in case it is something obvious. Thanks