Solved active oleobject command ?

August 6, 2012 at 03:01:39
Specs: Windows XP
is there an active oleobject command like "activesheets." or "activecells."
I want to have a toggle button change its text when pressed. only problem is i have 71.
they all need to do the same thing, and i was annoyed when i noticed that when copied and pasted the code isn't transfered too....
is there a simple way to do this?
Private Sub ToggleButton1_Click()
If ToggleButton1 = True Then
    ToggleButton1.Caption = "New"
Else
    ToggleButton1.Caption = ""
End If
End Sub


See More: active oleobject command ?

Report •


#1
August 6, 2012 at 06:49:04
Hi,

This is the only way i know of doing this, try it and let us know if it works

Private Sub ToggleButton1_Click()
    
    Dim oObject As OLEObject
    
    If ToggleButton1.Value = True Then

        For Each oObject In Sheet1.OLEObjects

            If Left(oObject.Name, 3) = "Tog" Then

                oObject.Object.Caption = "New"

            End If
        Next oObject
        
    ElseIf ToggleButton1.Value = False Then
    
        For Each oObject In Sheet1.OLEObjects

            If Left(oObject.Name, 3) = "Tog" Then

                oObject.Object.Caption = " "

            End If
            
        Next oObject
    End If
    
End Sub


Report •

#2
August 6, 2012 at 07:54:49
✔ Best Answer
heres what i ended up doing....
Mainly because there is no order to how the buttons are pressed.

Option Explicit

Private ETI(17 To 84) As Integer
Private ETISwapActive As Boolean



Private Sub SwapETIValue(tb As ToggleButton, i As Integer)
Dim S1 As String
Dim S2 As String
Dim s3 As String
Dim StoreETI

S1 = "Lifed Items"
S2 = "TBF(SEI)"
s3 = "NewETIClock"
StoreETI = "A1"

    If ETISwapActive Then Exit Sub
    ETISwapActive = True

    If tb.Value = True Then
        tb.Caption = "New"
        Dim OKValue As Boolean
        Dim OldETI As String
        
        Do
            OldETI = InputBox("Enter Old ETI Value Below")
            
            If Trim(OldETI) = "" Then
                OKValue = True
                tb.Value = False
                tb.Caption = ""
            ElseIf (Val(OldETI) > 0 And Int(Val(OldETI)) = Val(OldETI)) Then
                sheets(s3).Range(StoreETI).Offset(i, 0).Formula = Val(OldETI)
                OKValue = True
            End If
            
        Loop Until OKValue
    Else
        If (sheets(s3).Range(StoreETI).Offset(i, 0).Value > 0) Then
            If MsgBox("Delete Old ETI Value For Unit 17?", vbOKCancel + vbQuestion) = vbOK Then
                sheets(s3).Range(StoreETI).Offset(i, 0).Formula = 0
                tb.Caption = ""
            Else
                tb.Value = True
            End If
        Else
            tb.Caption = ""
        End If
    End If
    
    ETISwapActive = False
End Sub

Private Sub ToggleButton1_Click()
    SwapETIValue ToggleButton1, 17
End Sub
Private Sub ToggleButton2_Click()
    SwapETIValue ToggleButton2, 18
End Sub
Private Sub ToggleButton3_Click()
    SwapETIValue ToggleButton3, 19
End Sub
Private Sub ToggleButton4_Click()
    SwapETIValue ToggleButton4, 20
End Sub
Private Sub ToggleButton5_Click()
    SwapETIValue ToggleButton5, 21
End Sub
Private Sub ToggleButton6_Click()
    SwapETIValue ToggleButton6, 22
End Sub
Private Sub ToggleButton7_Click()
    SwapETIValue ToggleButton7, 23
End Sub
Private Sub ToggleButton8_Click()
    SwapETIValue ToggleButton8, 24
End Sub
Private Sub ToggleButton9_Click()
    SwapETIValue ToggleButton9, 25
End Sub
Private Sub ToggleButton10_Click()
    SwapETIValue ToggleButton10, 26
End Sub
Private Sub ToggleButton11_Click()
    SwapETIValue ToggleButton11, 27
End Sub
Private Sub ToggleButton12_Click()
    SwapETIValue ToggleButton12, 28
End Sub
Private Sub ToggleButton13_Click()
    SwapETIValue ToggleButton13, 29
End Sub
Private Sub ToggleButton14_Click()
    SwapETIValue ToggleButton14, 30
End Sub
Private Sub ToggleButton15_Click()
    SwapETIValue ToggleButton15, 31
End Sub
Private Sub ToggleButton16_Click()
    SwapETIValue ToggleButton16, 32
End Sub
Private Sub ToggleButton17_Click()
    SwapETIValue ToggleButton17, 33
End Sub
Private Sub ToggleButton18_Click()
    SwapETIValue ToggleButton18, 34
End Sub
Private Sub ToggleButton19_Click()
    SwapETIValue ToggleButton19, 35
End Sub
Private Sub ToggleButton20_Click()
    SwapETIValue ToggleButton20, 36
End Sub
Private Sub ToggleButton21_Click()
    SwapETIValue ToggleButton21, 37
End Sub
Private Sub ToggleButton22_Click()
    SwapETIValue ToggleButton22, 38
End Sub
Private Sub ToggleButton23_Click()
    SwapETIValue ToggleButton23, 39
End Sub
Private Sub ToggleButton24_Click()
    SwapETIValue ToggleButton24, 40
End Sub
Private Sub ToggleButton25_Click()
    SwapETIValue ToggleButton25, 41
End Sub
Private Sub ToggleButton26_Click()
    SwapETIValue ToggleButton26, 42
End Sub
Private Sub ToggleButton27_Click()
    SwapETIValue ToggleButton27, 43
End Sub
Private Sub ToggleButton28_Click()
    SwapETIValue ToggleButton28, 44
End Sub
Private Sub ToggleButton29_Click()
    SwapETIValue ToggleButton29, 45
End Sub
Private Sub ToggleButton30_Click()
    SwapETIValue ToggleButton30, 46
End Sub
Private Sub ToggleButton31_Click()
    SwapETIValue ToggleButton31, 47
End Sub
Private Sub ToggleButton32_Click()
    SwapETIValue ToggleButton32, 48
End Sub
Private Sub ToggleButton33_Click()
    SwapETIValue ToggleButton33, 49
End Sub
Private Sub ToggleButton34_Click()
    SwapETIValue ToggleButton34, 50
End Sub
Private Sub ToggleButton35_Click()
    SwapETIValue ToggleButton35, 51
End Sub
Private Sub ToggleButton36_Click()
    SwapETIValue ToggleButton36, 52
End Sub
Private Sub ToggleButton37_Click()
    SwapETIValue ToggleButton37, 53
End Sub
Private Sub ToggleButton38_Click()
    SwapETIValue ToggleButton38, 54
End Sub
Private Sub ToggleButton39_Click()
    SwapETIValue ToggleButton39, 55
End Sub
Private Sub ToggleButton40_Click()
    SwapETIValue ToggleButton40, 56
End Sub
Private Sub ToggleButton41_Click()
    SwapETIValue ToggleButton41, 57
End Sub
Private Sub ToggleButton42_Click()
    SwapETIValue ToggleButton42, 58
End Sub
Private Sub ToggleButton43_Click()
    SwapETIValue ToggleButton43, 59
End Sub
Private Sub ToggleButton44_Click()
    SwapETIValue ToggleButton44, 60
End Sub
Private Sub ToggleButton45_Click()
    SwapETIValue ToggleButton45, 61
End Sub
Private Sub ToggleButton46_Click()
    SwapETIValue ToggleButton46, 62
End Sub
Private Sub ToggleButton47_Click()
    SwapETIValue ToggleButton47, 63
End Sub
Private Sub ToggleButton48_Click()
    SwapETIValue ToggleButton48, 64
End Sub
Private Sub ToggleButton49_Click()
    SwapETIValue ToggleButton49, 65
End Sub
Private Sub ToggleButton50_Click()
    SwapETIValue ToggleButton50, 66
End Sub
Private Sub ToggleButton51_Click()
    SwapETIValue ToggleButton51, 67
End Sub
Private Sub ToggleButton52_Click()
    SwapETIValue ToggleButton52, 68
End Sub
Private Sub ToggleButton53_Click()
    SwapETIValue ToggleButton53, 69
End Sub
Private Sub ToggleButton54_Click()
    SwapETIValue ToggleButton54, 70
End Sub
Private Sub ToggleButton55_Click()
    SwapETIValue ToggleButton55, 71
End Sub
Private Sub ToggleButton56_Click()
    SwapETIValue ToggleButton56, 72
End Sub
Private Sub ToggleButton57_Click()
    SwapETIValue ToggleButton57, 73
End Sub
Private Sub ToggleButton58_Click()
    SwapETIValue ToggleButton58, 74
End Sub
Private Sub ToggleButton59_Click()
    SwapETIValue ToggleButton59, 75
End Sub
Private Sub ToggleButton60_Click()
    SwapETIValue ToggleButton60, 76
End Sub
Private Sub ToggleButton61_Click()
    SwapETIValue ToggleButton61, 77
End Sub
Private Sub ToggleButton62_Click()
    SwapETIValue ToggleButton52, 78
End Sub
Private Sub ToggleButton63_Click()
    SwapETIValue ToggleButton53, 79
End Sub
Private Sub ToggleButton64_Click()
    SwapETIValue ToggleButton54, 80
End Sub
Private Sub ToggleButton65_Click()
    SwapETIValue ToggleButton55, 81
End Sub
Private Sub ToggleButton66_Click()
    SwapETIValue ToggleButton56, 82
End Sub
Private Sub ToggleButton67_Click()
    SwapETIValue ToggleButton57, 83
End Sub
Private Sub ToggleButton68_Click()
    SwapETIValue ToggleButton58, 84
End Sub
Private Sub ToggleButton69_Click()
    SwapETIValue ToggleButton59, 85
End Sub
Private Sub ToggleButton70_Click()
    SwapETIValue ToggleButton60, 86
End Sub
Private Sub ToggleButton71_Click()
    SwapETIValue ToggleButton61, 87
End Sub


Report •

#3
August 6, 2012 at 08:05:32
Fair enough, if it works then you are on the right track. However my code can also be amended to accomodate the requirements, not that i am saying use my code at all, just saying in general. but i think when you figure something out yourself there is a good feeling of achievement, AND you actually learn a lot more. So nice work, glad you figured it out mate.

Report •
Related Solutions


Ask Question