Posts by Pesky Weasel

    Re: Vba Conditional Format Entire Workbook


    The code you have at the moment is in the change event for a particular worksheet.
    You need to place the same code in the


    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


    sub routine of the ThisWorkbook module.


    In the VBA editor open the module 'ThisWorkbook', this should be just under the module for the last worksheet in your workbook. In the dropdown menus just above the code window choose Workbook in the left and SheetChange in the right, then paste the code within the created subroutine.

    Re: VBA? Tack on extra characters after entering a value


    Just a word of warning for the custom format method:


    This will not change the underlying value of the cell. If you enter 20 in A1 the custom format will display '20-44' but a formula in B1 =A1+20 will return 40. (this will actually show '40-44' unless you have applied a format to B1).


    Also this will not work for formulas such as VLOOKUP. If you look for =VLOOKUP("20-44",A1:A9,1,0) you will get an error as the underlying value is only 20.


    Weasel

    Re: Array of Worksheet Names


    Sorry, go caught up at work...


    No you dont need to select to do most things however some things do need to be active to set properties:



    NB:
    1) I mentioned before the methods you were attempting were for cell ranges hence the .Cells qualifier
    2) In ordr to set the DisplayHeadings setting I activated each window as I looped through the array - cant promise its the best way but it works with little to no performance drop...
    3) There is no humour in arrays! :)


    Cheers
    Weasel

    Re: My daddy always said I'd go array...


    It would appear that you want the array to contain the names of sheets but it contains a mixture of possible names and ranges....


    The methods you are executing are for ranges not for the sheet object. Can you describe what you are trying to acheive and give some parameters?


    Cheers
    Weasel

    Re: Saving Cell Address by selecting It


    OK - as far as i can tell....


    By changing xl files via the taskbar you are leaving one instance of the application and entering another - the macro doesnt like this.


    You can prevent this from being an option by:


    Code
    1. With Application
    2. .ShowWindowsInTaskbar = False
    3. End With


    BUT (big BUT, like mine) it's rarely a good thing to mess about with a users interface. if you do, be sure to restore the users original setting.


    Regards
    Weasel

    Re: Saving Cell Address by selecting It


    Intersting that it doesnt work that way!


    I would not recommend rolling this out to other users with such a fine window of success. Will try to look at this some more to make it a little more bullet proof.


    Regards
    Weasel

    Re: VLOOKUP and Data Validation


    OK - Its been a long day, I'm tired and I've had a couple of Ales (from near where you are Dave - Little Creatures - second best Australian beer ever)


    It sounds like the VLOOKUP is supposed to return the name of your named range?


    If so try using:


    =INDIRECT(VLOOKUP(I2,$E$55:$F$65,2,FALSE))


    Regards
    Weasel

    Re: Saving Cell Address by selecting It


    Worked for me - maybe I just got lucky....


    I ran the macro from the book that contained the code. When the input box popped up I selected Window - and the file I wanted, then clicked on a cell.


    What are you doing when the code is not acheiving the desired result?

    Re: Complete Cell entry


    This code will pause the execution of the macro while the user enters a value but will carry on even if the users Cancels out of the inputbox.
    To force an answer try:


    Code
    1. Do While Range("E8").Value = ""
    2. stfn = InputBox("Please enter a Filename")
    3. Range("E8") = stfn
    4. Loop


    Regards
    Weasel
    \

    Re: Saving Cell Address by selecting It


    Hmmm - nice Rich! Like it...


    But AbuYehya, that isnt what you asked for the first time at all... Please try to specify as accurately as you can, what it is you need or time may be wasted generating useless solutions.


    What Rich has cunningly suggested will work with a small addition:


    Code
    1. Dim thisWKBK As String
    2. thisWKBK = ActiveWorkbook.Name
    3. Workbooks(thisWKBK).Sheets("Sheet1").Range("a1") = Application.InputBox("Please select a cell", Type:=8).Address(External:=True)

    Re: Complete Cell entry


    Try:


    Code
    1. .HtmlType = xlHtmlStatic
    2. If Range("E8").Value = "" Then
    3. stfn = InputBox("Please enter a Filename")
    4. Range("E8") = stfn
    5. End If
    6. .Filename = Range("E8").Value & ".mht"


    Regards
    Weasel

    Re: Saving Cell Address by selecting It


    Try placing this code in the module of the sheet that will be used to select the target cell:



    For good measure I prefer to set any variable such as this to False when the workbook opens to be sure there are no problems. In the ThisWorkbook module:


    Code
    1. Private Sub Workbook_Open()
    2. SelectOn = False
    3. End Sub


    Regards
    Weasel

    Re: Query on VBA


    Possibly this might be easier done without VBA. In the attached workbook I created 3 named ranges on sheet1 named Mumbai, Chennai & Bangalore.


    On sheet two I set up data validation (under the Data menu) to restrict the contents of B1 to a list that is populated by the named ranged entered in A1 [ =indirect($a$1) ]


    See if this works for you.


    Regards
    Weasel

    Files

    • validation.xls

      (13.82 kB, downloaded 293 times, last: )

    Re: Your opinion on searching a CSV file


    No, not at all. I am a relative new comer to VBA (or any type of code) and have never come across the syntax before.


    If most of the confusion occurs with street suffixs you could make the suffix a seperate entry using a list of all official abbreviations ie. CR, ST, PDE, CCT, RD and then concatenate the two entries as your search string.


    Cant help you with the code as I dont understand it :O)


    Cheers
    Weasel

    Re: Extract capitals from text


    Kinda hard to qualify that kind of fuzzy logic into code:


    Find capitals so long as they're not PO unless of course were talking about POTTS POINT, PORTSEA etc..
    Or find the left most 3 words unless the suburb is 2 words like MANLY WEST
    Or look for everything after rd, cr, st etc... unless there isnt one like 11 Tuna MANLY WEST


    Not sure you'll have much luck with an algorith to do this....


    You may have to try something like text to columns to seperate all the data and then work with it from there...


    Cheers
    Weasel