Posts by jjst34

    Is there a way for me to delete all sheets in a workbook by vba except sheet 1??? I know how to do it if they always keep the same name but the names of the other sheets are not always the same... I want like a select all except sheet 1... Any help is appreciated. Oh, Also, how can i make it bypass that message that says "all sheets will be deleted blah blah..."?? Thanks!



    Sheets(Array("Sheet1", "Sheet2")).Select
    ActiveWindow.SelectedSheets.Delete
    End Sub

    ok,, it doesn't seem to do anything.. on the part where it says
    Dim stOldPath As String
    doesn't that have to be stOldName?? I changed it.. still nothing,, but also,, I don't want it to look through every sheet.. just the current sheet, otherwise it would change a valid hyperlink?!then,, I just want to look for the Nov part, not the cell reference, otherwise wouldn't I need a macro for every hyperlink??
    sheet2!a1
    sheet!a2
    etc.. etc..

    Hi,, I have a worksheet that has hyperlinks on it, to go to another spot w/in the same worksheet... I create a new sheet monthly, so to create Decembers I just right click on Nov's and hit copy sheet and rename it. But now when I hit the hyperlinks in the new Dec sheet it obviously brings me to the spot in nov's since the hyperlink says "'Nov 03'!DK80". I have about 50 hyperlinks in the sheet.. How can I either change them all @ once to replace the Nov w/ Dec? Or when I copy the sheet have it auto change to the current sheets name?? Thanks a lot!!!

    By Golly, I think I've got it!! LOL... There may be an easier way to do what I asked, as far as selecting the first cell in a range.. But I did it the simple minded persons way! I just gave that cell a name and select the new range! LOL.. Here's what I hope is the final code.. I am going to mess around w/ it a little to make sure it's got no more bugs.. but here's what i've got..



    Thanks for all the help!! I really love this place!! :biggrin: :P

    this is getting good.. one more request before I think it's all perfect... lets say cells B2, B4 & B5 are empty,, they go to enter something into B5,, it'll say "enter in order blah blah", and put them into B4, because of the xlup and B3 has text in it. Is there a way to get them into cell B2 since that is actually the highest empty cell, i'm thinking to move the active cell to the top of the range "FY04_reduction_descriptions" then do an xldown... I just don't know how to force the active cell to the first cell in a range. get what i'm asking?? Again,, your help is appreciated!

    Derk,, Thanks.. that almost does exactly what I need... Only one problem.. Say they have text entered in cells B9:B13, which is correct, now they decide to clear the contents of B10, B12 & B13, your code does exactly what I want, which is nothing.. but then if they go to re-enter something in B13 (before B10 & 12), it now clears B11 also, because the cell before & after it are empty... LOL.. is there a way to say if any cell in the range before the active cell AND any cell in the range after the active one are <> "", then don't check?? would that work?

    well basically, if data has been filled in correctly in order, and someone decides later to remove one, even if they are just clearing the cell to later enter something different. I don't want it to delete all their data. Probably impossible. even in your code, if they delete say #2, it does what I want, but then if they delete #4 also, it freaks out, gives like 3 msgbox's and clears everything below... LOL...

    one more question about hiding toolbars... is there a way instead of hiding toolbars, to only show the one you want? The reason I ask, is if someone were smart, they'd create a custom toolbar in their personal.xls, then when they get to the sheet w/ all these hidden toolbars, their custom one is not hidden.

    hello.. I have this piece of code in my sheet..


    Basically what it does is require that if something is entered in a dollar amount field (column D), that a description must first be entered in column B. Also, They must enter a description in row order first as well.. For instance they cannot enter data in row 5 before row 2 etc... Now it works fine and dandy, except when the following happens.. They fill up say 10 rows of data, descriptions and values, no problem.. well now they want to remove #2 so they delete the description, well, now it wipes out everything below it too, losing everything entered.. boy i'd be pissed if this happened to me.. is there a cleaner way to write this code to prevent this?? Thanks!
    Just commenting out the clearcontents line doesn't quite make it much cleaner.. cuz then they get an unnecessary msgbox... any help is appreciated! Thanks!!
    Here is a copy for you to view to see what it does.. Thanks!
    http://home.ripway.com/2003-11/39141/tester.xls

    question for you.. I was looking at some spreadsheets my wife's work has and some of them do some cool things,, so I decided to try and look @ the code.. when I go into the VBA, and look @ modules or the worksheet, there is NO code in any of them?? How would they do this???

    I figured this one out w/ a couple adjustments.. I even fancied it up a little w/ an input box!! :) me is happy!! here is the code I am going to use...



    [code]
    Private Sub worksheet_change(ByVal Target As Excel.Range)
    Dim result As Range
    For Each result In Range("FY04_reduction_totals")
    If result < 0 And result.Offset(0, -6) = "" Then
    result.Offset(0, -1).Select
    Range(ActiveCell, ActiveCell.Offset(0, -3)).ClearContents
    result.Offset(0, -6).Select
    Range(ActiveCell, ActiveCell.End(xlUp)).Select
    Application.ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = InputBox("Please enter a description in cell " & ActiveCell.Address & " before you enter values, Thanks!")
    End If
    Next result
    End Sub


    Thx for your help in getting me started! !!

    i have had this problem before and what I do, not sure how it works on all versions,, but in XP which I have you go to Edit, links, then choose break links...
    Or, if you don't need some kind of formatting copy all the data on the sheet (ctrl+a) and paste special, values...
    or another case i've had to do is a "find" for ".xls" in the formulas, this will lead you to linked cells also.... and you can just copy/paste the values there as well.. hope this helps!

    Quote

    Originally posted by philby
    Where would we be without a bug or two!!!!!!!!!!


    And yes you have the same train of thought as the code's origional intention!!!


    If you get stuck let me know - Phil


    gotta love bugs... as far as being stuck.. when I put this in the worksheet change.. nothign happens, except excel froze up on me @ work using excel 97. @ home literally nothing happens... maybe you could test it out???

    I have decided to use this disable right click in a spreadsheet I use.. But occasionally I have to do some work w/ it.. Is there any way to write a macro that will allow right click while I edit the sheet, then disable it again when I'm done?? I know just go into the code, comment it out, then remove the comments when I'm done... Thanks!


    well it sorta wanted to make sense, but it kept locking up excel when i'd enter something into excel.
    I guess I just need it to say if any cell in range("whatever_range") is < 0 and the cell 7 cells before it is blank, then to bring up a messagebox saying, please enter a description. Thanks phil!!