98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel form populating word doc
Excel form populating word doc
Resolved · High Priority · Version 2010
Tue 18 Jun 2019, 15:43Delegate Vicki said...
Vicki has attended:
Excel VBA Introduction course
Excel form populating word doc
Hi, I want to tell the form to produce one document if option buttons 1 & 2 are true and another if option buttons 1& 3 are true, I have this for 4 scenarios and have done the following script, however it's not working, is there a way I can say if this AND this is true then this and if this is true but this is false then this, I've used 'and' but I think it's this that's upsetting it. it works fine if I only include one button without the 'and'.
THANK YOU! :)
If Me.OptionButton1 = True And Me.OptionButton11 = True Then
Set wapp = CreateObject("Word.Application")
Set wdoc = wapp.Documents.Open("\\emea\emeadata\Alesco\Financial Risks\Business Operations\FI\Templates\Quote Report Wizard\2019 UK Wholesale Reinsurance Quote Report1.dotx")
wapp.Visible = True
ElseIf Me.OptionButton2 = True And Me.OptionButton11 = True Then
Set wapp = CreateObject("Word.Application")
Set wdoc = wapp.Documents.Open("\\emea\emeadata\Alesco\Financial Risks\Business Operations\FI\Templates\Quote Report Wizard\2019 UK Retail Reinsurance Quote Report1.dotx")
wapp.Visible = True
ElseIf Me.OptionButton1 = True And Me.OptionButton11 = False Then
Set wapp = CreateObject("Word.Application")
Set wdoc = wapp.Documents.Open("\\emea\emeadata\Alesco\Financial Risks\Business Operations\FI\Templates\Quote Report Wizard\2019 UK Wholesale Insurance Quote Report1.dotx")
wapp.Visible = True
ElseIf Me.OptionButton2 = True And Me.OptionButton11 = False Then
Set wapp = CreateObject("Word.Application")
Set wdoc = wapp.Documents.Open("\\emea\emeadata\Alesco\Financial Risks\Business Operations\FI\Templates\Quote Report Wizard\2019 UK Retail Insurance Quote Report1.dotx")
wapp.Visible = True
End If
Tue 18 Jun 2019, 15:59Trainer Claire said...
RE: Excel form populating word doc
Hi Vicki,
I'm just working through your question and the logic you've used.
First question - and apologies if you're all to aware of this - do you know you can only have one option button set to true in a group.
If you have three options then setting one to true automatically sets the other to false. If you want more than one question answered via option buttons then you need to put each set of options and their answers in a frame.
If you want multiple options to one question then you need to use check boxes.
Can you confirm that you've got this bit set up correctly while I carry on working through your question?
thanks
Claire
Tue 18 Jun 2019, 16:08Delegate Vicki said...
RE: Excel form populating word doc
Hi Claire, thank you.
yes I know about the options buttons in a group, but have never used checkboxes before.
What I originally did was a group of 2 option buttons and one checkbox, I tried saying if the checkbox is true and option button 1 is true then open this, then if the checkbox was false and option button 1 was true then open this, again have this 4 times, each time only one of the grouped optionbuttons is checked as 'true' and the checkbox could be checked true or false, are you saying I need to change the optionbuttons to checkboxes? my current option buttons need to be grouped as you cannot choose both option button 1 AND 2, it has to be one or the other.
Does that make sense?
Thanks so much for your quick response
Vicki
Tue 18 Jun 2019, 16:10Delegate Vicki said...
RE: Excel form populating word doc
sorry, just to clarify, option button 11 is on it's own, not in a group. Option buttons 1 and 2 are in a group.
Tue 18 Jun 2019, 16:16Trainer Claire said...
RE: Excel form populating word doc
Hi Vicki,
I think you're buttons are fine. I was just making sure before we had checked the obvious before we dug much deeper.
I think you're logic is sound.
What I would do is something like this, just to check that you're going down the correct arm of you IF statement.
In my quick test I have opt1 and opt2 in a frame and opt 11 outside the frame on a user form.
Private Sub CommandButton1_Click()
If OptionButton1 = True And OptionButton11 = True Then
MsgBox "1 true and 11 true"
ElseIf OptionButton2 = True And OptionButton11 = True Then
MsgBox "2 true and 11 true"
ElseIf OptionButton1 = True And OptionButton11 = False Then
MsgBox "1 true and 11 false"
ElseIf OptionButton2 = True And OptionButton11 = False Then
MsgBox "2 true and 11 false"
Else
MsgBox "all false"
End If
Unload Me
End Sub
Just running that through pulls up the right message box.
Don't forget that if you do not check an option button or a check box it will have the value of false.
That should help work out where the problem is coming from. Let me know if you need more help.
thanks
Claire
Tue 30 Jul 2019, 15:08Delegate Vicki said...
RE: Excel form populating word doc
Hi, this is kind of related to this query so hence adding on to this thread. If I wanted to create much the same product but with an output of an Excel document rather than a word one, would I used the same code but just change the wapps/wdocs to something else? what do I use for xl? I've never actually done anything with an output of an excel doc.
Thanks
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Moving or Copying Sheets Between Workbooks in Excel 2010Here's how to move or copy sheets between workbooks in Excel 2010: |
