Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 10-06-2020, 01:46 AM
Guessed's Avatar
Guessed Guessed is offline Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Windows 10 Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Office 2016
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


I think this workbook will show the labels as you describe. I added the extra warning labels and changed the code to reveal them when necessary.
Attached Files
File Type: xlsm Mailing Label with help3.xlsm (48.3 KB, 6 views)
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #17  
Old 10-06-2020, 02:10 AM
kiwimtnbkr kiwimtnbkr is offline Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Windows 10 Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Office 2016
Advanced Beginner
Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

At a quick look at it, you are nothing short of a magician!

I will change the labels, markings, addresses and colours to what they need to be tomorrow to prove the 'proof of concept' and also have a look at your coding to see how you've worked your magic.

I am pretty damn positive that you've 100% nailed it, and allowed me to be able to put someone in their place, so I can't thank you enough and definitely

Would it be alright if I sent you a private message?

cheers
Mike
Reply With Quote
  #18  
Old 10-06-2020, 03:27 AM
Guessed's Avatar
Guessed Guessed is offline Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Windows 10 Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Office 2016
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

Quote:
Originally Posted by kiwimtnbkr View Post
Would it be alright if I sent you a private message?
Sure, no problem.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #19  
Old 10-06-2020, 04:43 PM
kiwimtnbkr kiwimtnbkr is offline Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Windows 10 Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Office 2016
Advanced Beginner
Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

Apart from one (hopefully) easily resolved minor glitch of the From and To combo boxes in the User Form auto-populating themselves with the first two rows of info from the Address table, everything else works beautifully!

But even better was the look on someone's face at work - they don't just have one egg of their face, they have the entire tray. It was truly something to behold

The glitch only appears in this version of the User Form. I've been through and triple checked the properties so I'm hoping it's an easily resolved issue. If not then I'll just insert two blank rows at the top of the address table and we can happily roll with that
Reply With Quote
  #20  
Old 10-06-2020, 05:18 PM
Guessed's Avatar
Guessed Guessed is offline Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Windows 10 Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Office 2016
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 glitch was put in there on purpose because I was sick of having to fill in two addresses while testing. Look in the code to where the Initialise is filling the list. There are two trailing lines that set the ListIndex value to 0 or 1. These lines are telling the control to select the first or second item in the list respectively. You can disable those two lines to leave them blank (Note that the macro fails if you haven't made a selection for both addresses before clicking the bottom button).

Disable the lines by adding a single quote at the start (or just delete them).
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #21  
Old 10-06-2020, 05:33 PM
kiwimtnbkr kiwimtnbkr is offline Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Windows 10 Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Office 2016
Advanced Beginner
Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

And with that, now very obvious, change - I am very happy to call this resolved!

(that little trick will come in handy for something else no doubt)

Hoping that my private message got thru - the offer stands, even more so if you happen to journey over the ditch

Thanks heaps for everything you've done, it's truly appreciated
Reply With Quote
  #22  
Old 10-07-2020, 11:39 PM
kiwimtnbkr kiwimtnbkr is offline Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Windows 10 Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Office 2016
Advanced Beginner
Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

Temporarily marking as 'unresolved'.

After getting some suggestions from colleagues, I put three form control command buttons onto the worksheet. Button 1 restarts the UserForm if it's been closed, button 2 causes the Print Preview and Print screen to open (same screen as File > Print would show) and button 3 causes the workbook to close without showing the 'save changes' prompt and they all work as expected when run within the worksheet.

The issue I have run into is when I press the command button on the Userform it throws the following error 'Run-time error 445: Object doesn't support this action'.

Clicking on the 'debug' button shows that it is erroring on the piece of code in red:

Code:
For Each aCtl In Me.frameMarking.Controls
    For Each aShape In aSheet.Shapes
      If aShape.Title = aCtl.Tag Then   <--this line here
        aShape.Visible = aCtl
      End If
    Next aShape
  Next aCtl
Is there an easy solution to this or am I too clever for my own good and having command buttons on the worksheet is not a good idea?
Reply With Quote
  #23  
Old 10-08-2020, 12:49 AM
Guessed's Avatar
Guessed Guessed is offline Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Windows 10 Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Office 2016
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

Or perhaps you are not clever enough for your own good - the buttons should be Ribbon buttons but that is a whole other ball game.

I'm not sure how you created the 'buttons' on the sheet but I'm hoping they aren't regular boxes of the same type we have been working with. If this is the case, this change to the code should work
Code:
  For Each aCtl In Me.frameMarking.Controls
    For Each aShape In aSheet.Shapes
      If aShape.Type = 1 Then
        If aShape.Title = aCtl.Tag Then
          aShape.Visible = aCtl
        End If
      End If
    Next aShape
  Next aCtl
If that doesn't work, you will need to post your workbook so I can see how your buttons differ from the labels.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #24  
Old 10-08-2020, 02:32 AM
kiwimtnbkr kiwimtnbkr is offline Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Windows 10 Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet Office 2016
Advanced Beginner
Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

And with two lines of code, this proof of concept is back to resolved.

MANY thanks!

cheers
Mike
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to link a cell from one worksheet to another gambo Excel 1 01-18-2019 10:26 AM
Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet How to populate a userform combobox from an excel worksheet jrooney7 Word VBA 14 09-16-2018 08:52 PM
Userform calls other userform, then populate worksheet Lehoi Excel Programming 0 02-03-2016 02:58 PM
link worksheet on shared file hifrank Word 0 06-24-2015 07:29 AM
Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet How do you copy a row from one worksheet to another? mars1886 Excel Programming 3 02-11-2014 02:12 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:56 PM.


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