Posts by richardcranney

    Re: to run code on worksheet activation


    Quote from Chaz1

    It works fine using sheet tabs ...BUt!
    I,m using hyperlinks to open sheets and for some reason it does seem to work with them.
    Any further ideas.


    Thanks
    Chaz


    Can you post a sample copy of your sheet, just like a basic part of it so i can have a play and get it working for you. I know hyperlinks you are limited, but ill see if there is anything.

    Re: Searching


    Norie, thanks


    Im getting to be a novice now, cant fathem it out. Is there a way of everytime it finds a result to select it on that sheet?


    ive used the following:
    [code]
    If MsgBox("Is this the right result", vbYesNo Or vbQuestion) = vbNo Then
    Carry on searching
    Else
    End
    Endif
    [code]


    What im wanting to do is show the result before it comes up with the message box and just selects the cell that it is in rather than highlighting them all.

    Re: Searching


    Quote from royUK

    Is this better?


    Thanks Roy for your input, nice to hear from you!


    Ive tried inputting the code you mentioned above, but it seems to only search the last page.
    On the sheet, as I had "hello" on the last sheet, i added a blank sheet to the end. Now when I run the search it just selects the last sheet. The code you have used it familiar to me, but to be honest, gone over my head a bit so dont know what would need changing. Ive added the workbook to see if you could help further.


    Thanks again

    Re: Searching


    On this, if I do a search is there a way of asking excel the following:

    Code
    If searchresult = false
      next sheet
    else
      highlight that cell
    endif


    I just dont know what variants to use to give the desired effect, hope someone can help with that.


    Thanks

    Re: Searching


    Quote from Dave Hawley

    Groups the Worksheets you want to search. That is, hold down Ctrl and select the needed sheets.


    Thanks dave, but ive tried that and it doenst work to the full effect. Office 97 doesnt have a "search All", you can select multiple sheets and then do find but it doesnt find it. Ill have to look at another way of doing it via the vba code

    Re: Searching


    Thanks for the reply, but using this it doesnt perform the search, not to what i can find out anyway.


    Does anyone have any ideas on how to search all sheets for a keyword?


    Its being used on Excel 97 as thats the only version we have at work due to the licence. I just can work out how to get a search to search one sheets, if its not there, change the sheet and search that one, until it finds the query.


    Any help would be much appriciated.

    Re: Searching


    Hi there,


    Sorry to bring this up again, just its baffling me, i dont understand it and was hoping somebody can help.


    Ive tried various different pieces of code, but they all produce the same error, changing the .value to .text etc etc.



    Ive had a search on the forum, but cant find any people trying to do the same thing as me, hope someone can help.


    Thanks again


    Rich

    Re: Searching


    Hi, just to add, its runtime error 91 that it comes up with, dont know if this will help, coz im stumped!


    Thanks again for all your help

    Re: Searching


    Hi, thanks


    is there a VBA code for selecting all sheets, as when i record the macro, it puts in the sheet names, where as each week there will be an additional sheet.

    Hello,


    Does anyone know a way of being able to search ALL the sheets in one go on a workbook? Going through all the sheets maybe one by one, i cant fathem it at all.


    Rich

    Hello, does anyone know if there is a prompt to alert the user when closing the workbook?


    Say a user closes the work book at the end of the day, im wanting excell to bring a prompt, or load a form, asking the user if they have entered the end of day figure to the sheet. They would then have a yes or no option.


    If yes selected, the workbook saves and closes as normal. if they select no, then the workbook stays open and directs to the part they need to fill in.


    Thanks

    Re: Not vba, but formula for working out week number




    Well, thank you both very much. Ive used the non ATP one that you have displayed above, and although it is the most complicated thing i have everseen in my life, and i dont understand it. It works. So thanks!

    Re: Not vba, but formula for working out week number


    No, its in all general calendars.


    If you have outlook on your computer, open it up and go to the calendar. Go to the options and select "calendar options", you will see there is an option for "show week numbers in calendar navigator", select this and you will see where all the months are shown on the left (on mine anyway) you will have the week number before.

    Re: Not vba, but formula for working out week number


    lol, had a look in a diary, and it was showing there as week 28.


    They have them in most diarys, like the 1st week of the year is classed as week1, the 2nd week is week 2 and so on.


    Obviously they'res only 52 weeks in a year, so just wondered if it was possible to work it out somehow.