cmdButton_Click()

[Doug Bell]

G'Day,
Sorry but one more question on Excel Forms,
I have a button on a toolbar with OnAction set to "dacOpenFrm(1)"
sub dacOpenFrm opens a form called frmAddlin

frmAddLin has a CommandButton control called "Cancel" with code:

Private Sub cmdCancel_Click()
Unload frmAddLine
End Sub

Everything works except that you have to click the button twice.
The first click doesn't do anything.
The form has the focus and you can move between controls but the first click
on the button doesn't do anything.

Doug

 

[Bob Phillips]

Hi Doug,

I just knocked up a test, and it worked fine. What do you mean by you set
the OnACtion to "dacOpenFrm(1)" on the toolbar? That is illegal syntax, it
doesn't work (at least not for me), as you can't name a macro that, and so
you can't set OnAction either.

Have you stripped your code down to the fundamentals, that is just the form
with a cancel button, and tested it?

any other things you are doing?


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Doug Bell]

G'Day Bob,
1. I mean by OnAction set to "dacOpenFrm(1)" that I have an XLA with code,
when it loads it creates a button on the toolbar call "NewLine":
Set objCmdBrPp = Application.CommandBars _
("Worksheet Menu Bar").Controls("Insert")
On Error Resume Next
Set objCmdBtn = objCmdBrPp.Controls(1)
If objCmdBtn.Caption <> "New Line" Then
Set objCmdBtn = objCmdBrPp.Controls("NewLine")
If Err.Number <> 0 Then
Set objCmdBtn = objCmdBrPp.Controls.Add _
(Type:=msoControlButton, before:=1)
End If
End If
On Error GoTo 0

' Set properties for the new menu item.
With objCmdBtn
.Caption = "New Line"
.OnAction = "dacOpenFrm(1)"
End With

2. I did try creating a new test form with just a "Cancel" button and this
works perfectly, one click and close.
My form is not complex and has little code.

When you click the Toolbar button (with OnAction = dacOpenFrm(1) )

The sub dacOpenFrm is called with passed argument (1)

The code:

Sub dacOpenFrm(intFrm As Integer)
Select Case intFrm
Case Is = 1
frmAddLine.Show
End Select
End Sub

frmAddLine is displayed.

The code in the form is fairly simple:


Option Explicit
Dim datAddDat As Date

Private Sub UserForm_Activate()
txtAddDate = Format(Date, "dd mmm yyyy")
datAddDat = txtAddDate
txtAddRow = "A:1"
txtAddRow.SetFocus
End Sub

Private Sub txtAddDate_AfterUpdate()
If IsDate(txtAddDate) Then
txtAddDate = Format(txtAddDate, "dd mmm yyyy")
datAddDat = txtAddDate
Else
MsgBox "Please enter a correct date"
txtAddDate = datAddDat
End If
End Sub

Private Sub cmdOK_Click()
Unload frmAddLine
End Sub

Private Sub cmdCancel_Click()
Unload frmAddLine
End Sub

I noticed that if I run a macro to display the form:
frmAddLine.Show

then the Cancel button runs correctly.

So it isn't a problem with the form or its code, I think it is the sub that
opens it, can I get the button that I create to open the form directly?

Thanks

Doug