Using Excel UserForm from Open Workbook in Outlook VBA

wpiet · External Since: Sep 16, 2009 Posts: 4

I have an Outlook macro that opens & processes an Excel workbook.
That workbook contains a userform that has textboxes with ControlSources
assigned to cells in the workbook.
Can I use that Excel userform in the Outlook macro?

"frmFormName.Show" gives me "Compile error: Variable not defined"

Alternatively, if I have to create the userform in Outlook, how do I assign
the Excel workbook cells to the ControlSource for the textboxes?

The intention is to show the userform with the current values of the cells
in the textboxes, allow the user to change those values in the textboxes and
have them populate the assigned cells in the workbook.
--
Will

Michael Bauer [MVP - Outl · External Since: Aug 04, 2006 Posts: 647

You can use a public method in the workbook to make the form accessible. For
instance, copy this into the Excel module ThisWorkbook:

Public Function ShowForm() As String
' show the form here, then maybe return what the user has entered
ShowForm = "whatever"
End Function

And this to Outlook:

Sub testxl()
Dim Xl As Object
Dim Wb As Object
Set Xl = GetObject(, "excel.application")
Set Wb = Xl.Workbooks(1)
Wb.ShowForm()
End Sub

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>

Am Wed, 16 Sep 2009 13:50:02 -0700 schrieb wpiet:

> I have an Outlook macro that opens & processes an Excel workbook.
> That workbook contains a userform that has textboxes with ControlSources
> assigned to cells in the workbook.
> Can I use that Excel userform in the Outlook macro?
>
> "frmFormName.Show" gives me "Compile error: Variable not defined"
>
> Alternatively, if I have to create the userform in Outlook, how do I
assign
> the Excel workbook cells to the ControlSource for the textboxes?
>
> The intention is to show the userform with the current values of the cells
> in the textboxes, allow the user to change those values in the textboxes
and
> have them populate the assigned cells in the workbook.

wpiet · External Since: Sep 16, 2009 Posts: 4

Thanks for the response, Michael.
I'm apparently missing something or doing something else wrong. Here's my
code (Office 2003):

In "This Workbook":

Option Explicit
Public Abt As String
Public Function ShowForm() As String
Abt = vbNo
frmForm.Show
ShowForm = Abt
End Function

In the userform (it has 2 textboxes, an OK button & CANCEL button):

Option Explicit
Private Sub cmdCancel_Click()
Abt = MsgBox("Are You Sure You Want To Cancel?", vbYesNo +
vbDefaultButton2)
If Abt = vbYes Then
Unload Me
Else
Me.txt1.SetFocus
End If
End Sub

Private Sub cmdOK_Click()
If Len(Trim(Me.txt1)) = 0 _
And Len(Trim(Me.txt2)) = 0 Then
MsgBox ("Are We Doing Anything Here?")
Me.txt1.SetFocus
Else
Abt = vbNo
Unload Me
End If
End Sub

Private Sub txt1_Change()
If Not IsNumeric.Me.txt1.Value Then
MsgBox ("Textbox 1 Value Not Numeric")
Me.txt1.SetFocus
ElseIf Me.txt1.Value < 20000000 Then
MsgBox ("Textbox 1 Value < $20,000,000")
Me.txt1.SetFocus
End If
End Sub

(code for "txt2" is the same)

In Outlook: (WB & XL are both DIMed & SET as in your code & the workbook is
open.)

Dim UsrAbt As String

With XL
.
.
.
UsrAbt = WB.ShowForm()
If UsrAbt = vbYes Then
.ActiveWorkbook.Close
.DisplayAlerts = True
Set WB = Nothing
Set XL = Nothing
Exit Sub
End If
.
.
.
End With

If I have just "WB.ShowForm()", I get "Compile error: Expected: =".

With the code as I have it [UsrAbt = WB.ShowForm()], when I step through it,
I get "Runtime error '438': Object doesn't support this property or method".

Anything in my code jump out at you as wrong?

Will

"Michael Bauer [MVP - Outlook]" wrote:

>
>
> You can use a public method in the workbook to make the form accessible. For
> instance, copy this into the Excel module ThisWorkbook:
>
> Public Function ShowForm() As String
> ' show the form here, then maybe return what the user has entered
> ShowForm = "whatever"
> End Function
>
> And this to Outlook:
>
> Sub testxl()
> Dim Xl As Object
> Dim Wb As Object
> Set Xl = GetObject(, "excel.application")
> Set Wb = Xl.Workbooks(1)
> Wb.ShowForm()
> End Sub
>
> --
> Best regards
> Michael Bauer - MVP Outlook
>
> : Outlook Categories? Category Manager Is Your Tool
> : VBOffice Reporter for Data Analysis & Reporting
> : <http://www.vboffice.net/product.html?pub=6&lang=en>
>
>
> Am Wed, 16 Sep 2009 13:50:02 -0700 schrieb wpiet:
>
> > I have an Outlook macro that opens & processes an Excel workbook.
> > That workbook contains a userform that has textboxes with ControlSources
> > assigned to cells in the workbook.
> > Can I use that Excel userform in the Outlook macro?
> >
> > "frmFormName.Show" gives me "Compile error: Variable not defined"
> >
> > Alternatively, if I have to create the userform in Outlook, how do I
> assign
> > the Excel workbook cells to the ControlSource for the textboxes?
> >
> > The intention is to show the userform with the current values of the cells
> > in the textboxes, allow the user to change those values in the textboxes
> and
> > have them populate the assigned cells in the workbook.
>

Michael Bauer [MVP - Outl · External Since: Aug 04, 2006 Posts: 647

I guess you missed the imported part: don't dim Wb as Workbook but as Object
else the compiler sees that an Excel.Workbook doesn't know custom method.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>

Am Thu, 17 Sep 2009 08:45:01 -0700 schrieb wpiet:

> Thanks for the response, Michael.
> I'm apparently missing something or doing something else wrong. Here's my
> code (Office 2003):
>
> In "This Workbook":
>
> Option Explicit
> Public Abt As String
> Public Function ShowForm() As String
> Abt = vbNo
> frmForm.Show
> ShowForm = Abt
> End Function
>
> In the userform (it has 2 textboxes, an OK button & CANCEL button):
>
> Option Explicit
> Private Sub cmdCancel_Click()
> Abt = MsgBox("Are You Sure You Want To Cancel?", vbYesNo +
> vbDefaultButton2)
> If Abt = vbYes Then
> Unload Me
> Else
> Me.txt1.SetFocus
> End If
> End Sub
>
> Private Sub cmdOK_Click()
> If Len(Trim(Me.txt1)) = 0 _
> And Len(Trim(Me.txt2)) = 0 Then
> MsgBox ("Are We Doing Anything Here?")
> Me.txt1.SetFocus
> Else
> Abt = vbNo
> Unload Me
> End If
> End Sub
>
> Private Sub txt1_Change()
> If Not IsNumeric.Me.txt1.Value Then
> MsgBox ("Textbox 1 Value Not Numeric")
> Me.txt1.SetFocus
> ElseIf Me.txt1.Value < 20000000 Then
> MsgBox ("Textbox 1 Value < $20,000,000")
> Me.txt1.SetFocus
> End If
> End Sub
>
> (code for "txt2" is the same)
>
> In Outlook: (WB & XL are both DIMed & SET as in your code & the workbook
is
> open.)
>
> Dim UsrAbt As String
>
> With XL
> .
> .
> .
> UsrAbt = WB.ShowForm()
> If UsrAbt = vbYes Then
> .ActiveWorkbook.Close
> .DisplayAlerts = True
> Set WB = Nothing
> Set XL = Nothing
> Exit Sub
> End If
> .
> .
> .
> End With
>
> If I have just "WB.ShowForm()", I get "Compile error: Expected: =".
>
> With the code as I have it [UsrAbt = WB.ShowForm()], when I step through
it,
> I get "Runtime error '438': Object doesn't support this property or
method".
>
> Anything in my code jump out at you as wrong?
>
> Will
>
>
> "Michael Bauer [MVP - Outlook]" wrote:
>
>>
>>
>> You can use a public method in the workbook to make the form accessible.
For
>> instance, copy this into the Excel module ThisWorkbook:
>>
>> Public Function ShowForm() As String
>> ' show the form here, then maybe return what the user has entered
>> ShowForm = "whatever"
>> End Function
>>
>> And this to Outlook:
>>
>> Sub testxl()
>> Dim Xl As Object
>> Dim Wb As Object
>> Set Xl = GetObject(, "excel.application")
>> Set Wb = Xl.Workbooks(1)
>> Wb.ShowForm()
>> End Sub
>>
>> --
>> Best regards
>> Michael Bauer - MVP Outlook
>>
>> : Outlook Categories? Category Manager Is Your Tool
>> : VBOffice Reporter for Data Analysis & Reporting
>> : <http://www.vboffice.net/product.html?pub=6&lang=en>
>>
>>
>> Am Wed, 16 Sep 2009 13:50:02 -0700 schrieb wpiet:
>>
>>> I have an Outlook macro that opens & processes an Excel workbook.
>>> That workbook contains a userform that has textboxes with ControlSources
>>> assigned to cells in the workbook.
>>> Can I use that Excel userform in the Outlook macro?
>>>
>>> "frmFormName.Show" gives me "Compile error: Variable not defined"
>>>
>>> Alternatively, if I have to create the userform in Outlook, how do I
>> assign
>>> the Excel workbook cells to the ControlSource for the textboxes?
>>>
>>> The intention is to show the userform with the current values of the
cells
>>> in the textboxes, allow the user to change those values in the textboxes
>> and
>>> have them populate the assigned cells in the workbook.
>>

wpiet · External Since: Sep 16, 2009 Posts: 4

Very insightful. That is exactly right. thanks.

Now that the userform opens, I find another problem there:

In the userform Sub cmdOK_Click(), I get "Compile error: Variable not
defined" on variable Abt for statement "Abt = vbNo". Abt was defined in
"ThisWorkbook" as a Public string.

This is the entire "ThisWorkbook" code:

Option Explicit
Public Abt As String

Public Function ShowForm() As String
Abt = vbNo
frmLottery.Show
ShowForm = Abt
End Function

Does the Private sub in the userform not recognize the Public variable
defined in the "ThisWorkbook" object, which is where the userform is accessed
from?
--
Will

"Michael Bauer [MVP - Outlook]" wrote:

>
>
> I guess you missed the imported part: don't dim Wb as Workbook but as Object
> else the compiler sees that an Excel.Workbook doesn't know custom method.
>
> --
> Best regards
> Michael Bauer - MVP Outlook
>
> : Outlook Categories? Category Manager Is Your Tool
> : VBOffice Reporter for Data Analysis & Reporting
> : <http://www.vboffice.net/product.html?pub=6&lang=en>
>
>
> Am Thu, 17 Sep 2009 08:45:01 -0700 schrieb wpiet:
>
> > Thanks for the response, Michael.
> > I'm apparently missing something or doing something else wrong. Here's my
> > code (Office 2003):
> >
> > In "This Workbook":
> >
> > Option Explicit
> > Public Abt As String
> > Public Function ShowForm() As String
> > Abt = vbNo
> > frmForm.Show
> > ShowForm = Abt
> > End Function
> >
> > In the userform (it has 2 textboxes, an OK button & CANCEL button):
> >
> > Option Explicit
> > Private Sub cmdCancel_Click()
> > Abt = MsgBox("Are You Sure You Want To Cancel?", vbYesNo +
> > vbDefaultButton2)
> > If Abt = vbYes Then
> > Unload Me
> > Else
> > Me.txt1.SetFocus
> > End If
> > End Sub
> >
> > Private Sub cmdOK_Click()
> > If Len(Trim(Me.txt1)) = 0 _
> > And Len(Trim(Me.txt2)) = 0 Then
> > MsgBox ("Are We Doing Anything Here?")
> > Me.txt1.SetFocus
> > Else
> > Abt = vbNo
> > Unload Me
> > End If
> > End Sub
> >
> > Private Sub txt1_Change()
> > If Not IsNumeric.Me.txt1.Value Then
> > MsgBox ("Textbox 1 Value Not Numeric")
> > Me.txt1.SetFocus
> > ElseIf Me.txt1.Value < 20000000 Then
> > MsgBox ("Textbox 1 Value < $20,000,000")
> > Me.txt1.SetFocus
> > End If
> > End Sub
> >
> > (code for "txt2" is the same)
> >
> > In Outlook: (WB & XL are both DIMed & SET as in your code & the workbook
> is
> > open.)
> >
> > Dim UsrAbt As String
> >
> > With XL
> > .
> > .
> > .
> > UsrAbt = WB.ShowForm()
> > If UsrAbt = vbYes Then
> > .ActiveWorkbook.Close
> > .DisplayAlerts = True
> > Set WB = Nothing
> > Set XL = Nothing
> > Exit Sub
> > End If
> > .
> > .
> > .
> > End With
> >
> > If I have just "WB.ShowForm()", I get "Compile error: Expected: =".
> >
> > With the code as I have it [UsrAbt = WB.ShowForm()], when I step through
> it,
> > I get "Runtime error '438': Object doesn't support this property or
> method".
> >
> > Anything in my code jump out at you as wrong?
> >
> > Will
> >
> >
> > "Michael Bauer [MVP - Outlook]" wrote:
> >
> >>
> >>
> >> You can use a public method in the workbook to make the form accessible.
> For
> >> instance, copy this into the Excel module ThisWorkbook:
> >>
> >> Public Function ShowForm() As String
> >> ' show the form here, then maybe return what the user has entered
> >> ShowForm = "whatever"
> >> End Function
> >>
> >> And this to Outlook:
> >>
> >> Sub testxl()
> >> Dim Xl As Object
> >> Dim Wb As Object
> >> Set Xl = GetObject(, "excel.application")
> >> Set Wb = Xl.Workbooks(1)
> >> Wb.ShowForm()
> >> End Sub
> >>
> >> --
> >> Best regards
> >> Michael Bauer - MVP Outlook
> >>
> >> : Outlook Categories? Category Manager Is Your Tool
> >> : VBOffice Reporter for Data Analysis & Reporting
> >> : <http://www.vboffice.net/product.html?pub=6&lang=en>
> >>
> >>
> >> Am Wed, 16 Sep 2009 13:50:02 -0700 schrieb wpiet:
> >>
> >>> I have an Outlook macro that opens & processes an Excel workbook.
> >>> That workbook contains a userform that has textboxes with ControlSources
> >>> assigned to cells in the workbook.
> >>> Can I use that Excel userform in the Outlook macro?
> >>>
> >>> "frmFormName.Show" gives me "Compile error: Variable not defined"
> >>>
> >>> Alternatively, if I have to create the userform in Outlook, how do I
> >> assign
> >>> the Excel workbook cells to the ControlSource for the textboxes?
> >>>
> >>> The intention is to show the userform with the current values of the
> cells
> >>> in the textboxes, allow the user to change those values in the textboxes
> >> and
> >>> have them populate the assigned cells in the workbook.
> >>
>

wpiet · External Since: Sep 16, 2009 Posts: 4

MIchael, I resolved the Abt issue by qualifying references to it:
"ThisWorkbook.Abt".

--
Will

"Michael Bauer [MVP - Outlook]" wrote:

>
>
> I guess you missed the imported part: don't dim Wb as Workbook but as Object
> else the compiler sees that an Excel.Workbook doesn't know custom method.
>
> --
> Best regards
> Michael Bauer - MVP Outlook
>
> : Outlook Categories? Category Manager Is Your Tool
> : VBOffice Reporter for Data Analysis & Reporting
> : <http://www.vboffice.net/product.html?pub=6&lang=en>
>
>
> Am Thu, 17 Sep 2009 08:45:01 -0700 schrieb wpiet:
>
> > Thanks for the response, Michael.
> > I'm apparently missing something or doing something else wrong. Here's my
> > code (Office 2003):
> >
> > In "This Workbook":
> >
> > Option Explicit
> > Public Abt As String
> > Public Function ShowForm() As String
> > Abt = vbNo
> > frmForm.Show
> > ShowForm = Abt
> > End Function
> >
> > In the userform (it has 2 textboxes, an OK button & CANCEL button):
> >
> > Option Explicit
> > Private Sub cmdCancel_Click()
> > Abt = MsgBox("Are You Sure You Want To Cancel?", vbYesNo +
> > vbDefaultButton2)
> > If Abt = vbYes Then
> > Unload Me
> > Else
> > Me.txt1.SetFocus
> > End If
> > End Sub
> >
> > Private Sub cmdOK_Click()
> > If Len(Trim(Me.txt1)) = 0 _
> > And Len(Trim(Me.txt2)) = 0 Then
> > MsgBox ("Are We Doing Anything Here?")
> > Me.txt1.SetFocus
> > Else
> > Abt = vbNo
> > Unload Me
> > End If
> > End Sub
> >
> > Private Sub txt1_Change()
> > If Not IsNumeric.Me.txt1.Value Then
> > MsgBox ("Textbox 1 Value Not Numeric")
> > Me.txt1.SetFocus
> > ElseIf Me.txt1.Value < 20000000 Then
> > MsgBox ("Textbox 1 Value < $20,000,000")
> > Me.txt1.SetFocus
> > End If
> > End Sub
> >
> > (code for "txt2" is the same)
> >
> > In Outlook: (WB & XL are both DIMed & SET as in your code & the workbook
> is
> > open.)
> >
> > Dim UsrAbt As String
> >
> > With XL
> > .
> > .
> > .
> > UsrAbt = WB.ShowForm()
> > If UsrAbt = vbYes Then
> > .ActiveWorkbook.Close
> > .DisplayAlerts = True
> > Set WB = Nothing
> > Set XL = Nothing
> > Exit Sub
> > End If
> > .
> > .
> > .
> > End With
> >
> > If I have just "WB.ShowForm()", I get "Compile error: Expected: =".
> >
> > With the code as I have it [UsrAbt = WB.ShowForm()], when I step through
> it,
> > I get "Runtime error '438': Object doesn't support this property or
> method".
> >
> > Anything in my code jump out at you as wrong?
> >
> > Will
> >
> >
> > "Michael Bauer [MVP - Outlook]" wrote:
> >
> >>
> >>
> >> You can use a public method in the workbook to make the form accessible.
> For
> >> instance, copy this into the Excel module ThisWorkbook:
> >>
> >> Public Function ShowForm() As String
> >> ' show the form here, then maybe return what the user has entered
> >> ShowForm = "whatever"
> >> End Function
> >>
> >> And this to Outlook:
> >>
> >> Sub testxl()
> >> Dim Xl As Object
> >> Dim Wb As Object
> >> Set Xl = GetObject(, "excel.application")
> >> Set Wb = Xl.Workbooks(1)
> >> Wb.ShowForm()
> >> End Sub
> >>
> >> --
> >> Best regards
> >> Michael Bauer - MVP Outlook
> >>
> >> : Outlook Categories? Category Manager Is Your Tool
> >> : VBOffice Reporter for Data Analysis & Reporting
> >> : <http://www.vboffice.net/product.html?pub=6&lang=en>
> >>
> >>
> >> Am Wed, 16 Sep 2009 13:50:02 -0700 schrieb wpiet:
> >>
> >>> I have an Outlook macro that opens & processes an Excel workbook.
> >>> That workbook contains a userform that has textboxes with ControlSources
> >>> assigned to cells in the workbook.
> >>> Can I use that Excel userform in the Outlook macro?
> >>>
> >>> "frmFormName.Show" gives me "Compile error: Variable not defined"
> >>>
> >>> Alternatively, if I have to create the userform in Outlook, how do I
> >> assign
> >>> the Excel workbook cells to the ControlSource for the textboxes?
> >>>
> >>> The intention is to show the userform with the current values of the
> cells
> >>> in the textboxes, allow the user to change those values in the textboxes
> >> and
> >>> have them populate the assigned cells in the workbook.
> >>
>