Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-24-2016, 03:29 PM
wlcdo2 wlcdo2 is offline AutoOpen to Update TextBox Value Windows 7 32bit AutoOpen to Update TextBox Value Office 2013
Novice
AutoOpen to Update TextBox Value
 
Join Date: Jun 2016
Posts: 17
wlcdo2 is on a distinguished road
Default AutoOpen to Update TextBox Value

I have a Word 2016 document that upon opening, I want an ActiveX Text Box to update with a random quote. When I run the code via Debug, it works perfectly 100% of the time, however when I open the document, it seems to always display the same quote (i.e. Quote 11) and I can't seem to figure out where I've gone wrong? This is my code within a standard Module:
Code:
Sub AutoOpen()
    
    Dim MyDocName As String
    Dim strQuotes(15) As String
    Dim lngIndex As Long
    MyDocName = Application.ActiveDocument.Name
    
    strQuotes(0) = "'Quote 1'"
    strQuotes(1) = "'Quote 2'"
    strQuotes(2) = "'Quote 3'"
    strQuotes(3) = "'Quote 4'"
    strQuotes(4) = "'Quote 5'"
    strQuotes(5) = "'Quote 6'"
    strQuotes(6) = "'Quote 7'"
    strQuotes(7) = "'Quote 8'"
    strQuotes(8) = "'Quote 9'"
    strQuotes(9) = "'Quote 10'"
    strQuotes(10) = "'Quote 11'"
    strQuotes(11) = "'Quote 12'"
    strQuotes(12) = "'Quote 13'"
    strQuotes(13) = "'Quote 14'"
    strQuotes(14) = "'Quote 15'"
    strQuotes(15) = "'Quote 16'"
     
    lngIndex = Int((15 - 0 + 1) * Rnd + 0)
     
    Documents(MyDocName).tbRandomQuote.Value = strQuotes(lngIndex)
         
End Sub
I thought perhaps the AutoOpen wasn't triggering, so I included a test MsgBox message and this displayed just fine so this suggests the AutoOpen is triggering OK. When I included the MsgBox, I did observe the TextBox update correctly, but when I click OK to the MsgBox message, the TextBox value changed back to 'Quote 11'.
I'm sure it'll be so simple, but I've pulled my hair out going around in circles.
Thanks so much for any assistance / guidance you could offer.



Corin.
Reply With Quote
  #2  
Old 07-24-2016, 07:15 PM
Guessed's Avatar
Guessed Guessed is offline AutoOpen to Update TextBox Value Windows 10 AutoOpen to Update TextBox Value Office 2013
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

The randomiser works for me but I changed it slightly to test.
Code:
    lngIndex = Int((16) * Rnd)
     MsgBox lngIndex & vbCr & strQuotes(lngIndex)
It wouldn't work if you had your code in a template because it needs a saved document path which you don't have when opening a new document. It also presupposes that that your document contains the tbRandomQuote object.

Could your issue be due to the timing of the AutoOpen macro actually running prior to the opening document becoming the 'ActiveDocument'?
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 07-25-2016, 12:34 AM
gmayor's Avatar
gmayor gmayor is offline AutoOpen to Update TextBox Value Windows 10 AutoOpen to Update TextBox Value Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

If you want the code to work when you create a new document, it should be in the template and be called AutoNew and not AutoOpen. If you want it to run when you open the document the code should be in the document (unless you are certain that the document will always be able to access the template) and the document saved as macro enabled.

Frankly I wouldn't use an active X text box for this. Better to use a docvariable, a content control or a bookmark. The following provides all the suggested options

Code:
Sub AutoOpen()

Dim MyDoc As Document
Dim strQuotes(15) As String
Dim lngIndex As Long
Dim oRng As Range
    Set MyDoc = Application.ActiveDocument

    strQuotes(0) = "'Quote 1'"
    strQuotes(1) = "'Quote 2'"
    strQuotes(2) = "'Quote 3'"
    strQuotes(3) = "'Quote 4'"
    strQuotes(4) = "'Quote 5'"
    strQuotes(5) = "'Quote 6'"
    strQuotes(6) = "'Quote 7'"
    strQuotes(7) = "'Quote 8'"
    strQuotes(8) = "'Quote 9'"
    strQuotes(9) = "'Quote 10'"
    strQuotes(10) = "'Quote 11'"
    strQuotes(11) = "'Quote 12'"
    strQuotes(12) = "'Quote 13'"
    strQuotes(13) = "'Quote 14'"
    strQuotes(14) = "'Quote 15'"
    strQuotes(15) = "'Quote 16'"

    lngIndex = Int((16) * Rnd)
    'MyDoc.tbRandomQuote.Text = strQuotes(lngIndex) 'Activex text box
    'MyDoc.SelectContentControlsByTitle("tbRandomQuote").Item(1).Range.Text = strQuotes(lngIndex) 'content control
    'MyDoc.Variables("tbRandomQuote").Value = strQuotes(lngIndex) 'docvariable - reproduce with docvariable field
    'MyDoc.Fields.Update 'update the docvariable field
    FillBM "tbRandomQuote", strQuotes(lngIndex)
    
End Sub

Private Sub FillBM(strBMName As String, strValue As String)
'Graham Mayor
Dim oRng As Range
    With ActiveDocument
        On Error GoTo lbl_Exit
        Set oRng = .Bookmarks(strBMName).Range
        oRng.Text = strValue
        oRng.Bookmarks.Add strBMName
    End With
lbl_Exit:
    Set oRng = Nothing
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto-update field codes in Outlook Template - AutoOpen Macro not working victoriasun Outlook 5 12-22-2015 01:54 AM
Update a summary chart when I update a dashboard with dates cangelis Excel 6 09-24-2014 08:08 AM
AutoOpen to Update TextBox Value Display result in textbox based on the input of another textbox scarymovie Word VBA 5 05-16-2012 07:05 PM
Textbox Template t0m46 Word 0 09-07-2010 03:38 AM
AutoOpen to Update TextBox Value Making a Macro "autoopen" Joshocom Word 1 03-16-2010 05:03 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:53 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft