Posts by onion456

    Re: select a row based on the text value of a cell

    you can use "Union" to select multiple rows. here is some code that i used arbitrary values to select the row i want, then add that selection to a range.

    the trick will then be to swap my arbitrary values with meaningful ones. i would use an array... go down each row and if a cell in that row contains the data you want, load the array with that row number. increment the array and do it again for the next row. then when you've fully loaded up your array, plug the array into the range object, then add the range to your multirange. make sense? i might be able to help with that if needed =)

    Hi all

    i was stepping thru some code and i think i halted a procedure while screenupdating was set false, now excel seems to be "set" as false. After shutting down excel, any workbook i open will not refresh its screen until i type an immediate "application.screenupdating = true" - any guesses? and yes, i did try to reverse the process by stepping thru some dummy code and halting while set to true =)


    Re: specifying a search with a forms box.


    1) Run the macro aaa. This will bring up the first userform
    2) Enter the word apple or bananas into textbox1
    3) Press the commandbutton
    4) IF the search yeilded more than one result...
    4a) gather results and add results to combobox
    4b) textbox1.hide,
    4c) select choice from combobox
    5) Press the button. This will show the selection you made, then close the form.

    Re: Passing Value

    one way s to use ByVal.. where the 2nd procedure would be written as:

    private sub autorun(byval arg1, byval arg2)
    msgbox xvariable1 & xvariable2
    end sub

    then call that procedure like this:

    call autorun(xvariable1,xvariable2)

    or another way altogether is to assign a cell you know should stay blank as a 'storage vessel' - assign it a value from one macro, then grab the data from it from the second macro (and clear the cell when youre done)


    Re: Autofiltering Using A Variable

    a little jiggering, and this seems to work...

    Sub Macro2()
        inp1 = InputBox("select greater than range")
        inp2 = InputBox("select less than range")
        Selection.AutoFilter Field:=3, Criteria1:=">" & inp1, Operator:= _
            xlAnd, Criteria2:="<=" & inp2
    End Sub

    Re: Autofiltering Using A Variable

    well, here is what the wonderful macro recorder has given me, if it points you in the right direction...

    Selection.AutoFilter Field:=3, Criteria1:=">3", Operator:=xlAnd, _

    Re: Assigning an Array element to select a different cell -Help

    sorry- been busy

    try this- modify to your liking:

    Re: Assigning an Array element to select a different cell -Help

    ok- you actually need 2 arrays here: 1 array as range, 1 array as value

    the range array would be the first part of your code, where you are setting the addresses.. but your wording is wrong- try this (untested)

    hope that helps!

    Re: Checkbox and userform confusion

    OR if you want to just have static values set, you can create an init procedure, like:

    private form_init()
    userform1.checkbox1 = true
    userform1.checkbox2 = false
    end sub

    then when you are ready to show your form, call the init procedure first.


    Re: Checkbox and userform confusion

    i have a similar issue. what i did is:

    create a new worksheet, named "options"
    assign each value a specific cell- like...

    option 1 - description in cells(1,1), value in cells(1,2)
    option 2 - description in cells(2,1), value in cells(2,2)

    hide the worksheet.

    then when you have the form open, have it initialize values from the hidden option sheet. i also made a "set options" button, so if you make changes on the userform, you can save them to the options sheet. that way when it wakes up again, it will have the new options already set.

    hope that helps :cheers:

    Re: Remove Modules via VBA Macro Code

    you can do it, you can even tell a module to delete itself. heres the code:

    Application.DisplayAlerts = False
        ActiveWorkbook.VBProject.VBComponents.Remove ActiveWorkbook.VBProject.VBComponents("Module1")


    Re: Adding time and date when saving WB.

    i just figgered this out today =)

    heres what i did

    Dim MyDate, MyStr
        MyDate = datestamp ' - a variable set to mm/dd/yyyy format- 
                    'datestamp created by use of =today() worksheet function      
                   'equal to saying:
      'mydate = "7/03/2006"       
       'this next line gets rid of the //'s
        MyStr = Format(MyDate, "mm_d_yyyy")

    now the variable "MyStr" = "7_03_2006", which you can use in a filename.

    check out 'Format' in the excel help file for possible combinations

    hope this helps!

    Re: multiple &quot;like&quot; conditions

    actually... the more i think about it, the more i doubt its an option.

    i actually need it to set the cell check to true based on an OR statement: such as:

    redwhitenbluecheck = activecell like "red" OR "white" OR "blue"

    anyway, if you have any input, its appreciated. =)

    Re: Add-In TextBox

    i think you could use an array, with a variable to reference the array address, such as:


    maybe that'll get you started? =)

    Hi all;

    I use 'like' statements in vba fairly regularly- example:

    passcheck = activecell like "*Pass*"
    'and then..
    if passcheck = true then
       totalpasscount = totalpasscount + 1
    end if

    i was wondering if anyone knew a way to add more than one condition to a "like" statement- such as...

    passcheck = activecell like "Pass","P","*ass"

    which would sure help cause then i wouldnt have to code the same cellcheck 3 times. anyone know if its possible?