|
|
Thread Tools | Display Modes |
#16
|
||||
|
||||
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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#17
|
|||
|
|||
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 |
#18
|
||||
|
||||
Sure, no problem.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#19
|
|||
|
|||
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 |
#20
|
||||
|
||||
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 |
#21
|
|||
|
|||
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 |
#22
|
|||
|
|||
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 |
#23
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#24
|
|||
|
|||
And with two lines of code, this proof of concept is back to resolved.
MANY thanks! cheers Mike |
|
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 |
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 |
How do you copy a row from one worksheet to another? | mars1886 | Excel Programming | 3 | 02-11-2014 02:12 PM |