Posts by JonathanVH

Yes, data validation should work for this. Create a range of the codes (including the numbers) and use that as a data validation list for a cell. Copy that across the row.

You can do that by enabling the Trust Center (Macro Settings) option "Trust access to the VBA project object model," but that's disabled for good reason. What you really should do is create a class (module) and instantiate a class object for each button. I know that's not "novice" level, but neither is self-modifying code.

It's not a database system, but should be sufficient for this. You can use a lookup range even when the worksheet is closed. Personally, I would connect to the relevant sheet using ADO (which is probably an advanced topic).

Apps I write are typically connected to a database, so I use a table in the database for this. I suppose you could have an Excel workbook act as the database, and that workbook need not be open.

Why not have a range with the value pairs and just use an Excel lookup function? That would be a lot easier to maintain (particularly for a user) than hard-coding the values in VBA (and you might not need VBA at all.)

Ace! For an issue like that, another answer is to format all the cells as text.

It should do both of those things. The .EntireRow.Delete line deletes the row that was moved, and the .Range.AutoFilter Field:=11 line removes the filter. Are you sure you copied all the above code?

Use VLOOKUP with INDIRECT, e.g., in cell J8:

=VLOOKUP(I8,INDIRECT($I$3&"!"&IF($I$6="ABC","D6:E7",IF($I$6="BGD","J6:K7",IF($I$6="CFD","P6:Q7",IF($I$6="FGH","V6:W7",IF($I$6="BHG","AH6:AI7",IF($I$6="NBD","AN6:AO7",IF($I$6="VFD","AT6:AU7",IF($I$6="ASE","AZ6:BA7",IF($I$6="CDX","BF6:BG7",IF($I$6="POI","BL6:BM7"))))))))))),2)

The macro recorder doesn't record keystrokes, it translates your actions into the equivalent VBA statements. If the button is not a custom button, and it creates an action, then that action should be translated into VBA code (although Microsoft does not cover everything). The VBA SendKeys statement can be used to send end keystrokes to the program as if they were typed, so if there is a accelerator key assigned to the button, that could work.

If the cell formatted as text is A1 and the cell formatted as number (General) is B1, then you could use VALUE(A1)=B1. If either cell could be formatted as text, then use VALUE(A1)=VALUE(B1).

Sure. As you evidently use the macro recorder, record opening and closing the workbook and then look at the code that generated.

When you get an error like that, click on Debug and it will highlight the line of code that threw the error message. In this case, I suspect that's the Windows("BBU Macro.xlsm").Activate line, which could be due to that workbook not being open.

Your idea of using AutoFill is better (faster):

Sub FillRows2()
Dim n As Long
  With Range("A3")
    n = Date - .Cells(0).Value2
    If n > 0 Then
      With Range(.Cells(1, 1), .Cells(1, 3))
        .FormulaR1C1 = Array("=R[-1]C+1", "=R[-1]C-10", "=R[-1]C+25")
        .AutoFill .Resize(n)
      End With
    End If
  End With
End Sub

You're welcome.

It is annoying, though, that VBA code like this is sometimes necessary. You should be able to just select the X range and then, holding down the Ctrl key, add the data range. Then use the wizard and choose the chart type. The wizard, though, as is their wont, will be capricious. Sometimes it correctly creates the chart from the range, but sometimes it won't. If you get it working and then record a macro to get the code, the code often won't work the same way. This is why I avoid creating all but the simplest charts from VBA. Jon Peltier is the human wizard of this realm.

Until one of those geniuses appears, you could try something like this:

Sub FillRows()
Dim cell As Range
  Set cell = Range("A2")
  While cell.Value2 < Date
    Set cell = cell(2)
    cell.FormulaR1C1 = "=R[-1]C+1"
    cell(, 2).FormulaR1C1 = "=R[-1]C-10"
    cell(, 3).FormulaR1C1 = "=R[-1]C+25"
  Wend
End Sub

A table in Excel has column filters by default. If you don't see those (they are dropdown arrows to the right of each header cell), then select any cell in your data and choose Format as Table from the Home ribbon. If a user then opens the filter on your "actions" column, they can simply uncheck Closed on the list and that will hide those rows.

Yes, they do behave differently, and there are more advanced properties available with the ActiveX version (and more possible with VBA). Another alternative would be to put the combo box on a form: all controls on a form are ActiveX (as that is their native object type) and they all (except RefEdit :duh:) work correctly there.

What version of Excel?

Can you just use a Forms control instead of an ActiveX control? ActiveX worksheet controls have always been a bit buggy...