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.


    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:
    If MsgBox("Is this the right result", vbYesNo Or vbQuestion) = vbNo Then
    Carry on searching

    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:

    If searchresult = false
      next sheet
      highlight that cell

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


    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


    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.


    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.


    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.


    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.