Posts by fifijazz

    This isn't a very elegant solution, but perhaps it will work for you. Modify the the code below to suit your worksheet. Set link updating to manual. Run the code below. Changed cells will now be red. Create a second macro to delete the old stuff in column AA and reformat the formula cells like the rest of your workbook when you are through.

    For Each targ In Sheets("sheet1").UsedRange.SpecialCells(xlCellTypeFormulas, 23)
    If InStr(2, targ.Formula, "[") Then
    Range("AA" & targ.Row) = targ.Value
    targ.FormatConditions.Add Type:=xlExpression, Formula1:="=" & targ.Address & "<>" & "AA" & targ.Row
    With targ.FormatConditions(1).Font
    .Bold = True
    .ColorIndex = 3
    End With
    End If
    End Sub

    I think you will need to use ActiveX controls from the Control toolbox. You are currently using controls from the Forms toolbar. ActiveX controls have a Visible property that you can set to True or False. You can also set the Enabled property of the control to True or False.

    I'm off to a meeting, but if you have trouble getting started, I'm sure someone else will help you. You may need to describe in more detail what you are trying to do.

    If I am understanding correctly what you are trying to do, this should work. If the number you want to input is in cell G1, you would:

    1. Create a dynamic named range for the column in which your X-axis values are. Let's assume they are in column A. We'll use the name "dyna". The formula for the name will be =offset($A$1,0,0, $g$1,1).

    2. In your chart Source Data, set the X-axis range to =Sheet1!dyna
    (You have to enter the sheet name or workbookname followed by an apostrophe.)

    Here you go. This will handle any future changes to the workbook and add comments only if the cell contains a forumula. If you already have formulas in the workbook you would like to comment, modify this code to loop through them and add the comments initially.


    For starters, press End, Home to find the last cell of your used range. If it is below and/or to the right of your data, delete ALL of the blank rows and columns in your worksheet, then save the file. Also eliminate any special formatting you don't really need. And yes, you really really ought to transfer this data to Access!


    The code below will work for a specific workbook. It must be copied into ThisWorkbook, not into a standard module. If you really want to set the orientation to landscape for every new workbook's current and future sheets, have a look at the NewWorkbook event of the Application object. In addition to setting the orientation for the existing sheets in the new workbook, your code would also have to copy the procedure below into the new workbook. Or at any rate, that's the only way I know of to do it. Hope this gets you started.

    Private Sub Workbook_NewSheet(ByVal Sh As Object)
       Sh.PageSetup.Orientation = xlLandscape
    End Sub

    More information is needed. Are these select queries or action queries? If they are action queries, what do they do? Do you want to return the data to Excel, or are you using Access by itself?

    Doing this is not going to delete anything. If you filter in place, it will hide the rows that are true duplicates. If you copy to another location, it copies only the unique rows but still will not delete anything from your matster list.

    If your list shows robbie williams - angels listed twice, filtering will hide one of those rows. robbie williams - gremlins would not be hidden, because it is not exactly the same as robbie williams - angels.

    Are you just trying to sort the rows, or are you wanting to end up with all the values in one cell? If you are just wanting to sort the rows, take a look at "Sort a list" in Excel help. If your rows don't sort correctly, the cells probably contain text, not numbers--even though they might LOOK like numbers.

    The easiest way is to click on any cell in your list. Then go to Data>Filter>Advanced Filter. Either filter in place, or copy to another location, as you choose. Be sure to check the "Unique Records only" box at the bottom of the dialog box.

    Can you be more specific? There must be something about the rows that allows you to identify that you don't need them. Or, something about the rows you want to keep that differentiates them from the others.

    Worked okay for me when I tested it with the little sub below on a worksheet that contained formulas linked to the referenced workbook.

    Set ws = ActiveSheet
            With ws.Range("a2:a12")
                Set linkString = .Find("[AttainmentDB.xls]Database!", LookIn:=xlFormulas)
                If Not linkString Is Nothing Then
                    MsgBox ("Link found in " & ws.Name)
                 End If
            End With

    Can you post the code you used when you tried it out? If you copied and pasted from the snippet I posted, could it be that I mis-typed the substring that you are looking for in your linked formulas?