Posts by btadams

    I've created an add-in with some functions. If I Go to Tools -> Add-ins and load it up there are no problems. However, when I close and re-launch Excel there is an error message about editing a hidden workbook and the line that seems to be causing the error is:

    Application.MacroOptions macro:="INTERPOLATE", Category:=4

    any ideas as to why? Thanks,

    this may work. You may need to change the Range("A1") to whatever cell you want to start the selection in.

    Sub NameCells()
    Dim cell As Range

    Range("A1", Range("A1").End(xlDown)).Select
    For Each cell In Selection
    cell.Name = cell.Offset(0, 1).Value
    Next cell
    End Sub

    This example will copy the usedrange of a worksheet to a hidden worksheet each time the workbook is closed. It also puts the date and user name above the copied data. To implement this, right-mouse click on the Excel icon next to the File menu and choose View Code. Then change the drop-down that says (General) to Workbook. Then in the drop-down next to it, select BeforeClose and then paste this in:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Sheets("Hidden Sheet").Visible = True
    Sheets("Hidden Sheet").Activate
    If Range("A1").Value = "" Then
    Range("A1").End(xlDown).Offset(1, 0).Select
    End If
    ActiveCell.Value = Date
    ActiveCell.Offset(0, 1).Value = Environ("Username")
    Sheets("Data Sheet").UsedRange.Copy
    ActiveCell.Offset(1, 0).Select
    Application.CutCopyMode = False
    Sheets("Hidden Sheet").Visible = False
    End Sub

    You need to have a formula like this:

    =ADDRESS(2,H4) &":"&ADDRESS(100,H4)

    where cell H4 is the linked cell for the first drop-down (p.s. are you using the drop-downs from the Forms toolbar?)

    Then you need to create a named range (Insert -> Name -> Define) called MyRange and in the Refers to: box put the formula


    where H6 is the cell containing the Address functions


    to see all of Excel's statistical functions, go to the Insert menu and choose Function then when the Insert Function window pops up click on the Statistical category. If you then select a function you can click on the little yellow question mark to open up the help file for that function

    Aladin Akyurek gave me this formula:


    where $B$1:$D$1 is the range of cells containing vendor names and B2:D2 is the range with the prices

    One way is to put buttons from the Forms toolbar for each custom view, then use the macro recorder to generate code like this:

    Sub MyView1()

    End Sub

    and then right-mouse click on the button and assign the macro to the button

    as an example say we have a regression equation y = mx + b where m = 3 and b = 100 and we want to know what value of x will give us a value for y = 300. Enter the formula =3*A1 +100 in cell B1 and in cell A1 enter 1. Then go to Tools -> Goal Seek and select cell B1 (our formula) as the Set Cell, equal to 300 by changing cell A1

    I think the only way to do this is to put some code in the Workbook_Open event. With the workbook in question open, Right-mouse click on the Excel icon to the left of the File menu and choose View Code. Then in the drop-down menu where it says (General) select Workbook. By default the Workbook_Open event pops up. Put a line like this in there:


    Need a better explanation. Do you want an inputbox so a user can enter a name and the database will filter on that name and copy some stuff to another worksheet?

    You'll want to set the range references to absolute when you fill the formula down: