Replies

Have a look at the Excel.officeUI file in C:users%username%appdatalocalmicrosoftoffice

You’d be better off using the CustomUI part of the add-in to do your ribbon customisations – then it won’t matter where the file is.

I think you’ll need a loop like this:

Code:

Sub Clear_Data()
    Dim Sh                    As Integer
    Dim LR                    As Long
    Dim cell                  As Range

    Application.ScreenUpdating = False
    For Sh = 3 To Worksheets.Count
        With Worksheets(Sh)
            LR = .Cells(.Rows.Count, "A").End(xlUp).Row
            If LR < 15 Then LR = 15
            On Error Resume Next
            For Each cell In .Range("A15:M" & LR).SpecialCells(xlCellTypeConstants).Cells
                If cell.DisplayFormat.Interior.ColorIndex = xlColorIndexNone Then cell.ClearContents
            Next cell
            On Error GoTo 0
        End With
    Next Sh
    Application.ScreenUpdating = True
End Sub

The first thing I would suggest is removing the reference to ActiveSheet completely:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
'---------------------------------------
'TOGGLE PRINT CHECKS (COLUMN P)
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
    CHECKS.Show
    Target.Offset(0, 1).Select
End If
If Not Intersect(Target, Columns("P:P")) Is Nothing Then
    If Target.Value = "" Then
        Target.Value = "a"
    Else:
        Target.Value = ""
    End If
    Target.Offset(0, -1).Select
End If
End Sub

since it really shouldn’t be there anyway (as your sheet might not be active at the time).

I think you basically just need to change the function type to Variant rather than String, and add a line to CDbl the result.

The short answer is no, I’m afraid. The only function that does anything similar is IFERROR. For other functions you need to use a helper column.

You need to access those properties of the actual control through the Object property of the OLEObject itself:

Code:

.Object.ShowDropButtonWhen = fmShowDropButtonWhenFocus

There is no workaround – you can’t add functions to that dropdown, and there is no way to calculate what you need with a calculated field either. If you have Power Pivot it might be possible. Failing that, you’d have to use formulas outside (or instead of) your pivot table.

7369.2054 has the fixes in. Did you try to update?

There were a few bugs with forms introduced in the September updates, but the latest release should fix them. Have you tried updating your Office installation? Which build do you have?

That is not what I see, nor what I would expect. If the box will fit on the screen when it is underneath the drop down, that is where it should appear; otherwise, it will appear to the right.

To be clear, I mean that you should scroll the screen so that B10 is the first visible row in the sheet.

It’s the combobox trying to fit the whole list on screen without obscuring the dropdown part. (if you scroll the screen up, the list appears where it does for the earlier rows).

You can also just double any quotes inside the formula string:

Code:

Range("K2:K100").Formula = "=IF(OR(B2=""HOL"",B2=""VAC""),B2,E2)"

As I said – right-click the Plant field, choose Sort, then ‘more sort options’ and choose to sort using the value field.

You need to sort the plant field using the values data. Right-click the plant field, Sort – More sort options)

Code:

Dim sFilename as String
sFilename = Dir("C:some path" & Company & "*.xlsx")
if sFilename  vbNullString then
Workbooks.Open Filename:=sFilename,ReadOnly:=True, UpdateLinks:=0
... rest of code
End If

where the Company variable has your company number in it.