Replies

I did not explain myself well… here is my code…

Code:

Private Sub Workbook_Open()
    AddIns("Analysis ToolPak").Installed = True
    AddIns("Analysis ToolPak - VBA").Installed = True
    
    Dim cmbBar As CommandBar
    Dim cmbControl As CommandBarControl
     
    Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
    Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar
    With cmbControl
        .Caption = "&Sort" 'names the menu item
        With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item
            .Caption = "Sort 1" 'adds a description to the menu item
            .OnAction = "SortFunc_Prog" 'runs the specified macro
            .FaceId = 1098 'assigns an icon to the dropdown
        End With
    End With

    
End Sub

Again… what is my code to make sure that the “Add-Ins” tab displays. TIA.

DennisK

Thanx so much to both of you… I used Zeddy’s approach because I really didn’t need to display a message about it being deleted. Just that it was gone and as such… recreated the button… FYI..

Code:

Private Sub chkShape7()
    On Error Resume Next
    zShapeID = ActiveSheet.Shapes("Button 7").ID
    If IsEmpty(zShapeID) Then CreateButton7
    On Error GoTo 0
End Sub

Code:

Sub CreateButton7()
    ActiveSheet.Buttons.Add(143, 10, 143, 40).Select
    Selection.Name = "Button 7"
    Selection.OnAction = "PrintMenu"
    ActiveSheet.Shapes("Button 7").Select
    Selection.Characters.Text = "Print Menu"
    ActiveSheet.Shapes.Range(Array("Button 7")).Select
    With Selection.Font
        .Name = "Times New Roman"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("B7").Select
End Sub

DennisK

Thanks so much for your input… was hoping to avoid vba as the person I am doing this for wants it as simple as possible but will take a look at it.

Thanx so much for the input… that did not solve the problem either… guess I will have to live with it and use google as my work around.

That was not checked BUT… in planning for the future, I had placed “Option Explicit” in the code. Once I commented that out all seems to work just fine. Thanx SO much for pointing me in the right direction.

Dennis

Thanx so much but this is not an option… IT dept won’t allow permissions to do that. I did find a possible workaround over at Ozgrid that I will try.

THAT’S IT!!! Thanks so much.

OK… will upload a file called 0test0. If you look on the tab called “Data” at cell D18 you will see that the cell if formatted to display 5 digits… the actual value in the cell is 0 (zero). If you then look at the tab called “LinkedSheet” at cell D14 you will see that the cell is blank. I did change the formula to try to look at the way that Alan Wade suggested but to no success.

Try this (adjust the range as necessary)

=SUMPRODUCT(–(MOD(COLUMN(A1:H1),2)=0),–(A1:H1″”))

This does the trick. Thanx so much

Jan…

Thanx so much… worked like a dream.

Dennis

Legare…

Thanx so much for your input. However… the code DOES work. It forces any lower case entry to capitals throughout the range. Maybe it shouldn’t work…but it does. If a person makes an entry in a cell and hits Enter… the entry is forced to upper case. If a person makes an entry and just moves the cursor… the entry stays as lower case until they arrow over the entry and then it is forced to upper case. I have already used up the 3 conditional formattings allowed in the worksheet… that is why I needed the fourth. I have since added a fifth and anticpate more. And you are absolutely correct about the Case Else… I have deliberately left it out until I am sure that I have all the other conditions set… I will then add the case else.

Again…thanx so much for your help with this.

Dennis

Legare…

Thanx so much for your input. However… the code DOES work. It forces any lower case entry to capitals throughout the range. Maybe it shouldn’t work…but it does. If a person makes an entry in a cell and hits Enter… the entry is forced to upper case. If a person makes an entry and just moves the cursor… the entry stays as lower case until they arrow over the entry and then it is forced to upper case. I have already used up the 3 conditional formattings allowed in the worksheet… that is why I needed the fourth. I have since added a fifth and anticpate more. And you are absolutely correct about the Case Else… I have deliberately left it out until I am sure that I have all the other conditions set… I will then add the case else.

Again…thanx so much for your help with this.

Dennis

Hans & Legare…

Thanx so much for your input. Worked like a champ. Here is the actual code I ended up using.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Application.EnableEvents = False
On Error Resume Next
If Not Application.Intersect(Target, Range(“H8:BI12”)) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Dim oCell As Range
If Intersect(Target, Range(“H8:BI12”)) Is Nothing Then Exit Sub
For Each oCell In Intersect(Target, Range(“H8:BI12”))
Select Case oCell.Value
Case “S”
oCell.Font.Bold = True
oCell.Font.ColorIndex = 10
End Select
Next oCell
Application.EnableEvents = True
End Sub

As you can see it forces the value to upper case and colors the font bold green.

Dennis

Hans & Legare…

Thanx so much for your input. Worked like a champ. Here is the actual code I ended up using.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Application.EnableEvents = False
On Error Resume Next
If Not Application.Intersect(Target, Range(“H8:BI12”)) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Dim oCell As Range
If Intersect(Target, Range(“H8:BI12”)) Is Nothing Then Exit Sub
For Each oCell In Intersect(Target, Range(“H8:BI12”))
Select Case oCell.Value
Case “S”
oCell.Font.Bold = True
oCell.Font.ColorIndex = 10
End Select
Next oCell
Application.EnableEvents = True
End Sub

As you can see it forces the value to upper case and colors the font bold green.

Dennis

Worked like a charm!! Thanx so much.

Dennis