Getting text from Excel to Word

[McCees]

Dear ladies and gentlemen,

First of all, English is not my native speach, but I will try to make sense.

I just started with VBA on Word and Excel and it's quite fun!

I'm trying to make a excelsheet with a userform. When the userform is filled in, a commandbutton (when clicked on) will put the text in the right row.

This is all for a complaint document for my work. So the complaint is now filled in Excel. That's all okay! But we as company want to send a letter to the costumer to send a compensation. And what a double work to insert all the data twice! In excel and word. So I thought there would be a way to make it easier.

When all the data is inserted in the userform and click on the commandbutton to put it in the Excelsheet, I want to simultaniously get the data in Word on specific places. I hope I make sense.

I sure want to learn something, and I'm quite new to this all. So please be patient with me, haha!

The code I have now is as followed:

Private Sub cmdBrief_Click()
If Me.txtAchternaam.Value = "" Then
        MsgBox "Iedereen heeft een achternaam! Achternaam invullen!", vbCritical
        Exit Sub
    End If
If Me.txtAdres.Value = "" Then
        MsgBox "Meeste mensen die bij de McDonald's komen hebben een adres! Adres invullen!", vbCritical
        Exit Sub
    End If
If Me.txtPostcode = "" Then
        MsgBox "Als de gast een adres heeft, heeft deze ook een postcode en plaats! Postcode en plaats invullen!", vbCritical
        Exit Sub
    End If
If Me.txtDatum = "" Then
        MsgBox "Als er een klacht is, dan is deze op een bepaalde datum binnengekomen! Datum invullen (DD-MM-JJJJ)!", vbCritical
        Exit Sub
    End If
If Me.cboMeneerMevrouw.Value = "" Then
        MsgBox "Meneer of mevrouw? Zo moeilijk is dat niet. Graag aanklikken wat van toepassing is!", vbCritical
        Exit Sub
    End If
If Me.cboGStele.Value = "" Then
        MsgBox "Gastenrelatie of telefonisch? Graag aanklikken wat van toepassing is!", vbCritical
        Exit Sub
    End If
If Me.txtVerkeerd = "" Then
        MsgBox "Als er een klacht is, is er iets verkeerds gegaan! Graag opschrijven wat er verkeerd is gegaan!", vbCritical
        Exit Sub
    End If
If Me.cboVergoeding.Value = "" Then
        MsgBox "Gasten die een klacht hebben, krijgen altijd van ons een vergoeding. Graag aanklikken uit de lijst welke van toepassing is!", vbCritical
        Exit Sub
    End If
If Me.txtManager.Value = "" Then
        MsgBox "Diegene die dit invult, heeft ook een naam gekregen. Graag je eigen voornaam invullen!", vbCritical
        Exit Sub
End If
Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Klachten 2018")
    Dim n As Long
n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row
sh.Unprotect "1078"
sh.Range("A" & n + 1).Value = Me.txtDatum.Value
    sh.Range("B" & n + 1).Value = Me.txtManager.Value
    sh.Range("C" & n + 1).Value = Me.cboGStele.Value
    sh.Range("D" & n + 1).Value = (Me.txtVoornaam + " " + Me.txtAchternaam)
    sh.Range("E" & n + 1).Value = Me.txtAdres.Value
    sh.Range("F" & n + 1).Value = Me.txtPostcode.Value
    sh.Range("G" & n + 1).Value = Me.txtNummer.Value
    sh.Range("H" & n + 1).Value = Me.txtVerkeerd.Value
    sh.Range("I" & n + 1).Value = Me.cboVergoeding.Value
    sh.Range("J" & n + 1).Value = Me.cboMeneerMevrouw.Value
sh.Protect "1078"
Me.txtVoornaam.Value = ""
    Me.txtAchternaam.Value = ""
    Me.txtPostcode.Value = ""
    Me.txtAdres.Value = ""
    Me.txtDatum.Value = ""
    Me.txtVerkeerd.Value = ""
    Me.txtManager.Value = ""
    Me.txtNummer.Value = ""
Me.cboVergoeding.Value = ""
    Me.cboMeneerMevrouw.Value = ""
    Me.cboGStele.Value = ""
MsgBox "Nieuwe klacht is toegevoegd!", vbInformation
Unload Me
End Sub

Private Sub cmdCancel_Click()
Unload Me
End
End Sub

Private Sub cmdOngedaan_Click()
Me.txtVoornaam.Value = ""
    Me.txtAchternaam.Value = ""
    Me.txtPostcode.Value = ""
    Me.txtAdres.Value = ""
    Me.txtDatum.Value = ""
    Me.txtVerkeerd.Value = ""
    Me.txtManager.Value = ""
    Me.txtNummer.Value = ""
Me.cboVergoeding.Value = ""
    Me.cboMeneerMevrouw.Value = ""
    Me.cboGStele.Value = ""
End Sub

Private Sub UserForm_Activate()
With Me.cboVergoeding
        .Clear
        .AddItem ""
        .AddItem "1x McFlurry"
        .AddItem "2x McFlurry"
        .AddItem "1x medium menu"
        .AddItem "2x medium menu"
        .AddItem "1x burger naar keuze"
        .AddItem "2x burger naar keuze"
End With
With Me.cboMeneerMevrouw
        .Clear
        .AddItem ""
        .AddItem "Meneer"
        .AddItem "Mevrouw"
End With
With Me.cboGStele
        .Clear
        .AddItem ""
        .AddItem "Gastenrelaties"
        .AddItem "Telefonisch"
        .AddItem "Overig"
End With
End Sub

userform.jpg
This is how the userform looks like.

Kind regards,

Cees

[macropod]

Before clearing the userform (e.g. immediately before or after 'sh.Protect "1078"'), you might use code like:

'declare variables for word
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
wdApp.Visible = True

'add a new word document
Set wdDoc = wdApp.Documents.Add(Template:="complaint document template")

'update the document
With wdDoc
  .Bookmarks("Datum").Range.Text = Me.txtDatum.Value
  .Bookmarks("Manager").Range.Text = Me.txtManager.Value
  .Bookmarks("GStele").Range.Text = Me.cboGStele.Value
  .Bookmarks("Voornaam").Range.Text = Me.txtVoornaam.Value
  .Bookmarks("Achternaam").Range.Text = Me.txtAchternaam.Value
  .Bookmarks("Adres").Range.Text = Me.txtAdres.Value
  .Bookmarks("Postcode").Range.Text = Me.txtPostcode.Value
  .Bookmarks("Nummer").Range.Text = Me.txtNummer.Value
  .Bookmarks("Verkeerd").Range.Text = Me.txtVerkeerd.Value
  .Bookmarks("Vergoeding").Range.Text = Me.cboVergoeding.Value
  .Bookmarks("MeneerMevrouw").Range.Text = Me.cboMeneerMevrouw.Value
End With

Do note that the above code assumes the use of early binding, so you will need to set a reference to Word via the VBE's Tools>References. You will also need to supply the full path & name for your complaint document template where I've inserted "complaint document template".


PS: When posting code, please format it and use the code tags. Without both, you code is much harder to read than it need be.

[McCees]

Dear Marcropod,

Thanks for replying on my message!

Also, a big thank you for the effort! It works like clockwork.

Only left with 2 questions.

Now, when I submit the userform in excel with the commandbutton, it opens word, but it doesn't pop up in front op de excel sheet. Is there a line of code which does this?

The second question, when I submit the userform in excel with the commandbutton, I want to save the excelsheet. Is there a line of code which does this?

The help is really appriciated!

Kind regards,

Cees

[macropod]

You could add:

Thisworkbook.Save
wdApp.Activate

before you unload the userform.