Posts by d c

    Doh! =O

    I thought I had checked that. Clearly not.


    ----------

    Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Not Application.Intersect(Target, Range("A1:ZZ10000")) Is Nothing Then

    Target(1).Value = UCase(Target(1).Value)

    End If

    Application.EnableEvents = True

    End Sub

    -----------


    Now the mystery is who put that in there, and WHY? What useless code.


    Thanks!

    Playing around a little....

    If I select the table's cells and paste into a new book (Ctrl-C, Ctrl-N, Ctrl-V) I can put formulas in with no issues.

    If I then do a Ctrl-C, Ctrl-V back to the original spreadsheet, I have the same issues. So it seems to be related to the sheet/workbook.


    If I copy the Worksheet into a new book, the issue persists

    Stumped on this one.


    A colleague was using one of our common spreadsheets, doing some copy/pasting from another similar sheet when he called me over.

    His formulas were converting themselves to numbers.


    I saved the sheet on our network drive (the usual location) and took a look.

    If I, for example, go to cell H8 and enter "=SUM(H9:H24)" as soon as I hit enter I see the answer ($10,000). But there is no longer a formula in H8. It's a value: $10,000.

    Doesn't matter which cell I put the SUM into.

    Doesn't matter if I make a more complex argument, i.e. IF(....)

    As soon as I hit enter, the cell converts to the answer of the formula, and the formula is gone.


    Likewise, if I hit F2 and ENTER on any of the pre-existing formulas on the sheet, they convert to the answer.


    Ideas?

    Thanks!
    Dave


    Oh, I totally agree with you. If it was *my* workbook, they would be separated. Unfortunately, it's one of those inherited workbooks where you have to work with what you've been dealt.

    Correction, I THOUGHT I had deleted everything except 3 rows on that sheet.


    A little about the worksheet:
    This worksheet has an upper table and a lower table. Both extensively use =INDEX() to pull data from another worksheet.
    The tables don't connect to each other at all. The upper table is "here's what we're working on", the lower table is "potential work". Both pull from a separate worksheet in the same workbook. Put the project number in the proper cell and the INDEX formulas fill everything in.
    I need index formulas and not just a copy/paste of data, because that other worksheet gets updated with current status, which then is pulled into this worksheet.
    Hopefully that makes sense.


    Anyhow....
    I've been working on adding a row into the lower table.
    In additional experimenting, with the big workbook open, I found that if I did a Clear-FORMATS on the 36 formula-containing rows in the UPPER table, the insert-row VBA works just fine.
    If I simply select the upper table and do a DELETE to clear the cells (and leave the formatting) the VBA does NOT work.


    What the heck??

    It gets more weirder....maybe...


    I thought I'd simplify things...
    Copied that worksheet into a new book, so I don't have to worry about anything else in that large workbook I was in.
    Copied the VBA code into a new macro in the new workbook.
    SAME PROBLEM


    Deleted everything on that sheet except for the 3 rows closest to where I was trying to add a row. Same problem.


    Closed the "donor" workbook where that sheet originated.
    Macro works fine.


    So it doesn't appear to be a VBA-coding issue so much as something going on with that workbook in general.


    Doesn't make me any less stumped though. :?

    If you are trying to add a line with formatting and formulas VBA is unnecessary. Use Excel's Table Format and formulas and formats will expand as rows are added.
    [h=1][SIZE=12px]Overview of Excel tables[/SIZE][/h]


    You should also use the Witth ... End With Statement correctly


    Dim FoundCell As Range


    Thanks Roy. No luck though.


    As far as VBA vs no-VBA goes, this is a spreadsheet for others to use, so it'll be a "Click this button, put data in the highlighted cell, and let the formulas pull the proper info from another sheet"


    I moved the End With with no change.
    Changed Rows to .Rows with no change.


    "Pipeline:" is in row 49, so I hard-coded the line to add (again with no change)
    .Rows(50).Insert


    This is weird.


    I actually stripped the code down to this, and it still exits after inserting the row:


    [VBA]Sub InsertPipelineRow()
    '
    ' InsertPipelineRow Macro
    '


    '
    'Dim FoundCell As Range
    Dim PipelineRow As Double


    With ActiveSheet
    ' Set FoundCell = .Cells.Find(What:="PIPELINE:", After:=.Cells(1, 1), _
    ' LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
    ' SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)


    'PipelineRow = FoundCell.Row
    PipelineRow = 49


    '.Rows(PipelineRow + 1).Insert , CopyOrigin:=xlFormatFromRightOrBelow
    Rows(50).Insert , CopyOrigin:=xlFormatFromRightOrBelow
    'VBA doesn't make it below this point
    'I added the simple bit of math in the next line so I had a point to put a stop in the macro. It never gets there.


    PipelineRow = PipelineRow + 1 - 1
    FormatNewRow
    End With


    End Sub[/VBA]


    I have done it with and without the CopyOrigin with no change (other than which format it selects of course)

    I'm stumped with this one.
    I have VBA code that finds the row number that contains specific text ("Pipeline:"), selects the next row down, and inserts a row. Simple enough, right?
    More code will come after that, like copy/pasting some formulas, but the VBA mysteriously exits as soon as it inserts that new row. Nothing I put below it will run.



    [VBA]
    Sub InsertPipelineRow()
    '
    ' InsertPipelineRow Macro
    '


    '
    Dim FoundCell As Range
    Dim PipelineRow As Double


    With ActiveWorkbook.ActiveSheet
    Set FoundCell = .Cells.Find(What:="PIPELINE:", After:=.Cells(1, 1), _
    LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    End With
    PipelineRow = FoundCell.Row


    Rows(PipelineRow + 1).Insert , CopyOrigin:=xlFormatFromRightOrBelow
    'VBA doesn't make it below this point


    'I added the simple bit of math in the next line so I had a point to put a stop in the macro. It never gets there.
    PipelineRow = PipelineRow + 1 - 1
    'Copy/Pasting formulas would come after this....


    End Sub[/VBA]


    Thanks,
    Dave

    I have a stacked bar chart that I'm using as a Gantt chart. Naturally, Excel assumes where "zero" is for the start of the chart, and seeing as how the first series is formatted as no-fill & no-border, the chart doesn't start where I need. So I'd like to use Worksheet_Change code to automatically set the x-axis to be ~1 week before the start date and the end ~1 week after the end date.


    Start Date is in cell C17. The corresponding date value is a formula in Y17 (i.e. (C17)-7 in general format)
    End Date is in cell G23. The corresponding date value is a formula in Y18


    The chart really only needs to update when either of these two cells are changed, but changing it after every worksheet modification is fine. It's not a complicated sheet otherwise, and I don't want the end user to have to manuall change the chart.


    Since the values I want are formulas, I disregarded using Select Case Target.Address and I'm trying the following

    Code
    1. Option Explicit
    2. Private Sub Worksheet_Change(ByVal Target As Range)
    3. ActiveSheet.ChartObjects("Chart 4").Chart.Axes(xlCategory).MinimumScale = [Y17]
    4. ActiveSheet.ChartObjects("Chart 4").Chart.Axes(xlCategory).MaximumScale = [Y18]
    5. End Sub


    Excel gives me an error, saying "MinimumScale" method of Axis Object failed.
    Microsoft seems to think that error is tied to a protected workbook, which mine isn't.



    Am I on the right track with my code, or is there an easier way to skin this cat?


    Thanks!
    Dave



    *EDIT* SOLVED
    The obvious, yet subtle was doing me in. My chart is a HORIZONTAL stacked bar chart. Therefore, the horizontal axis isn't "X-axis = xlCategory" as I had written. It's the Value axis. DOH! Changed Category to Value and it's working fine.


    Thanks to all who took the time to look though. I'm still open to learning anything more efficient/better than the code I have.