Using Excel UserForm from Open Workbook in Outlook VBA

Status
Not open for further replies.
W

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.

Will
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
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

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.
 
W

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
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
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

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 " 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
>

>> >> >>

>

>
>> 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.

> >
 
W

wpiet

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 " 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

> > >

> 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 " 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
> >
> >> > >> > >>

> >
> >
> >> 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.
> >>

>
 
W

wpiet

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

"ThisWorkbook.Abt".

Will

"Michael Bauer " 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

> > >

> 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 " 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
> >
> >> > >> > >>

> >
> >
> >> 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.
> >>

>
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
S Find a cell value in excel using outlook vba Using Outlook 1
N Export details to a excel spreadsheet using macros Using Outlook 0
D send email from Excel using outlook template Outlook VBA and Custom Forms 3
M Outlook 2013 won't convert Excel contacts into Outlook contacts using my custom form Using Outlook 3
M using Folders.Count in Excel Vba Outlook VBA and Custom Forms 8
M VBA Send Sales reports using .oft files, originate in Outlook or Excel? Using Outlook 5
B Using Outlook from Excel Outlook VBA and Custom Forms 2
M Disable Contact Card Results when using "Search People" in Outlook Ribbon Using Outlook 4
K can't get custom form to update multiple contacts using VBA Outlook VBA and Custom Forms 3
S Outlook VBA How to adapt this code for using in a different Mail Inbox Outlook VBA and Custom Forms 0
pcunite Outlook 2019/O365 Build 13127.20408 errors when using MAPI calls Using Outlook 1
B Change Font and Font size using VBA Outlook VBA and Custom Forms 9
M Outlook 2013 reminder email by using Outlook vba Outlook VBA and Custom Forms 2
X Using Outlook 2013 and Outlook 365 Using Outlook 1
A Going to folder using shortcuts Using Outlook 3
A Outlook replies not using "delivered to" address in From Using Outlook 1
Terry Sullivan E-Mails Sent Using a Group Box Result in 70 Kickbacks Using Outlook 5
K Using Outlook 2016 to draw Using Outlook 1
O Outlook 365 - suddenly unable to send using Gmail POP3 Using Outlook 10
N Disable Auto Read Receipts sent after using Advanced Find Using Outlook 4
G Outlook 2016 sync contacts directly between phone and computer using outlook 2016 Using Outlook 0
L Moving emails with similar subject and find the timings between the emails using outlook VBA macro Outlook VBA and Custom Forms 1
O Save attachments using hotkey without changing attributes Outlook VBA and Custom Forms 1
J Add an Attachment Using an Array and Match first 17 Letters to Matching Template .oft to Send eMail Outlook VBA and Custom Forms 2
A Edit subject - and change conversationTopic - using VBA and redemption Outlook VBA and Custom Forms 2
A Using or not using apostrophes in search terms has this changed? Using Outlook 0
O Office 365 using POP3 on both laptop and desktop Using Outlook 0
M Using field names to capture a data element Using Outlook 0
B Vba to monitor time to respond to emails using a shared mailbox Outlook VBA and Custom Forms 5
B Looking to get the Recipient email address (or even the "friendly name") from an email I am replying to using VBA Outlook VBA and Custom Forms 4
D Using a VBA Custom Form to Send Reoccurring Email Upon Task Completion Outlook VBA and Custom Forms 4
Z Adding dropdown list using custom form Outlook VBA and Custom Forms 7
O Using .OST and .PST mail thru different providers Using Outlook 5
N Open & Save VBAProject.Otm using VBA Code Outlook VBA and Custom Forms 1
D Remove text in subject using VBA Outlook VBA and Custom Forms 4
P How to export voting results using VBA? Outlook VBA and Custom Forms 2
E Using the Like operator properly Outlook VBA and Custom Forms 1
R Using "check for duplicates" for existing contacts Using Outlook 2
N Using email notification to update calendar events? Outlook VBA and Custom Forms 4
S Macro using .SendUsingAccount only works the first time, after starting Outlook Outlook VBA and Custom Forms 4
C Sync Calendars using WiFI Using Outlook 3
A Capturing Send Variables without using Application_ItemSend in ThisOutlookSession Outlook VBA and Custom Forms 8
L Using alpha numeric in email address Using Outlook 5
Sabastian Samuel HOW DO I FORWARD AN EMAIL WITH MACRO using an email that in the body of another email Outlook VBA and Custom Forms 3
D create an html table in outlook custom form 2010 using vba in MsAccess Outlook VBA and Custom Forms 7
D Print Attachments only in selected emails using a macro Outlook VBA and Custom Forms 3
B query outlook using vba Outlook VBA and Custom Forms 13
M Using conditional formatting on a shared calendar with categories Using Outlook 6
e_a_g_l_e_p_i A question about installing office 2013 Pro and using my .pst from office 2010 Using Outlook 12

Similar threads

Top