Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet

Guessed · #1 10-06-2020, 05:18 PM

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).

kiwimtnbkr · #2 10-06-2020, 05:33 PM

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

kiwimtnbkr · #3 10-07-2020, 11:39 PM

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?

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