Posts by h

hi there

i use this line inside an addin

Application.Calculation = xlCalculationManual

only when I open the XL App, the addin is uploding, so when excution comes to the line ,
it gives this error:
Run Time Error 1004
Method calculation of object Application Failed

however, when the XL App is already openned, then uploading or unloading the Addin cause no problem

Re: Multiple Textboxes To Accept Only Numbers

u can use this: its a soultion of a member of the forum

Private Sub tot_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    ' Numeric values only
    Select Case KeyAscii
 
   Case 48 To 57         ' 0 to 9
    Case 43 To 46        ' (43 +)(44 ,)(45 -)(46 .)
    'Case 43                ' +
    'Case 44                ' 
    'Case 45                ' -
    'Case 46                ' period (.)
     Case 49               ' backspace
    
    Case Else
        KeyAscii = vbNull
    End Select


End Sub

Re: Delete CommandBar Control in 2007

Andy, thank u v much:smile:

Re: Delete CommandBar Control in 2007

Thank u Andy,
but i am really a bit confused what to do exactely ? specially that i did not get what u ment here

Quote

If you run the delete code on a menu item created with your code it will work.

i wish to be helped in this if possiable

Re: Delete CommandBar Control in 2007

Thank u Dave 4 reply

could u pls show me how 2 define the Tag property in my code ? Thank u v much

Re: Delete CommandBar Control in 2007

Thank u Andy

when excution comes 2

On Error Resume Next 'Just in  case
     '*Delete existing Super Menu if it was left.
     Application.CommandBars.FindControl(Tag:="MyMainMenuTag").Delete 
    On Error Goto 0

an error 91 appears tells : object variable or with block variable not set
this error come in openning and closing.

what i did wrong ?

Re: Delete CommandBar Control in 2007

ok, here is it

Private Sub Workbook_Open()
Dim cControl As CommandBarPopup


   On Error GoTo Workbook_Open_Error


On Error Resume Next 'Just in case
   '*Delete existing Super Menu if it was left.
'*****the error appears here, it tells :invalid procedure call or argument.
Application.CommandBars("Worksheet Menu Bar").Controls("Accountant Menu").Delete
On Error GoTo 0
        
   '*Add new Accounting Menu
    Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add(msoControlPopup, 1, , , True)
        
        With cControl
             .Caption = "Accountant Menu"
             .Tag = "MyMainMenuTag"
        End With
     
'* Add SubMenu 1
    With cControl.Controls.Add(Type:=msoControlButton, Temporary:=True)
        .Caption = "&Excel ShortCut Menu"
        .OnAction = "GreatMenu"
        .Style = msoButtonIconAndCaption
        .FaceId = 343 '176
        '.State = msoButtonDown ' or msoButtonUp
        '.Enabled = True ' or False
    End With
Workbook_Open_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Workbook_Open of VBA Document ThisWorkbook"


End Sub

Re: Delete CommandBar Control in 2007

Professionals..........Thank u

RoyUK,
creating your menu, is the code to do so under xl 2007 can be easly followed ?
if yes, is it possiable to post the code................thank u

Andy,
my case is same as your

Quote

the old Commandbar code should work.
A new control will be added to the Addin tab.

could u pls show me how 2 use .FINDCONTROL method and delete, because my addin works ok under XL 2007, only the problem of deleting the commadndbar which is added under the Addin tap ?

Thank u

Re: Delete CommandBar Control in 2007

thanks 4 reply

is there a work around ?

hi there
i use xl 2007
when open xl app my addin is installing, but when excution gets to delete the my addin an error appears tells :invalid procedure call or argument.

sub workbook_open
On Error Resume Next 'Just in case
 '*Delete existing Super Menu if it was left.
Application.CommandBars("Worksheet Menu Bar").Controls("Accountant Menu").Delete
On Error GoTo 0

End Sub

Any help why is this ? Thank u v much

Re: Number Format In Comment

Thank u v much.................now it worked

hi there
I am tring to writh the value of the cell formated as "#,##0.000" inside the comment box in this format,...............but ...........could not make it
here is my try

Sub Test()
Dim cf As Double, c As Range


Selection.ClearComments
    '* Add Comment:
    For Each c In Selection
        If c <> 0 And IsNumeric(c) Then
[B]cf = Format(c, "#,##0.000")[/B]
            With c
                .AddComment "hmk :" & Chr(10) & "   The Amount Was " & cf
                .Comment.Visible = True
                .Font.Color = -10477568
                .Comment.Shape.AutoShapeType = msoShapeRoundedRectangle
                .Comment.Shape.Select True
                With Selection
                    .Font.Size = 9
                    .Font.Bold = True
                    .Font.Color = -10477568
                    .ShapeRange.Shadow.Visible = msoTrue
                    .ShapeRange.Adjustments.Item(1) = 0.3
                    .HorizontalAlignment = xlRight
                End With

End Sub

any idea ? Thank u

Re: Screen Saver

thank u

hi there
i am trying to lunch screensaver using this code, which worked with Excel 2003, but not with Excel 2007 which i am using at the moment.
when excution comes 2 :

VBA.Shell "C:\WINDOWS\system32\ss3dfo.scr /S", vbMaximizedFocus

it tells file not found
any idea how 2 make it work in Excel 2007 ?

Sub RunScreenSaverProgram()
'*To show the screen saver
VBA.Shell "C:\WINDOWS\system32\ss3dfo.scr /S", vbMaximizedFocus
End Sub

Re: Add Cell Comment With Text

Thank u all, really you helped me alot

Dave, mikerickson, thomach, Stanley :gift::gift::gift::gift:

hi there
any idea to these 2 errors ?

if choose a cell then the code works fine, but when i choose a selection of cells
(ex. A1 A2A3 A4) then the code fail to excute in this line: Invalid proceduer call or argument error

.AddComment "hmk :" & Chr(10) & " value was " & ActiveCell.Value

and then fail to excute in this line: typr mismatch error

Selection.Value = (Selection.Value) * r

now here, does have to be loop to solve

here is the full code

Sub test()
Dim r as Integer
ActiveCell.ClearComments


'* Add Comment:
    With ActiveCell  'Selection
        .AddComment "hmk :" & Chr(10) & " value was " & ActiveCell.Value
        .Comment.Visible = True
        .Comment.Shape.AutoShapeType = msoShapeRoundedRectangle
        .Comment.Shape.Select True
        .Comment.Shape.AutoShapeType = msoShapeRoundedRectangle
        .Comment.Shape.TextFrame.AutoSize = True
    End With
End Sub




If r = 0 Or r = "" Then
Exit Sub
Else


'*Exchang Rate Box
Selection.Value = (Selection.Value) * r
End If

Re: Textbox Value Inside Comment

Thank u royUK

Re: Textbox Value Inside Comment

Thank u royUK :gift:, i got my mistake.....................

it is interesting to take into considration your comment about an error handler to detect a comment & delete it before adding the new one

could you pls show me how to detect a comment ? Thank u

On a userform, i put a TextBox1
i need to show the value of the Textbox1 inside the comment
i tried this way, but the comment shows r insted of its value.

any idea ? Thank u

Sub ok_Click
Dim r
r = TextBox1.Value


'* Add Comment:
    With ActiveCell
        .AddComment "hmk :" & Chr(10) & "" '" The Value was " & r 
        .Comment.Visible = True
        .Comment.Shape.AutoShapeType = msoShapeRoundedRectangle
        .Comment.Shape.Select True
    End With
End Sub

Re: Number Of Months

ByTheCringe2 and Andy Pope both of you, thank u very much

both of you replied, Andy Pope post an advice but ByTheCringe2 posted a direct(;) ) soultion

thanks to how creats this web page