Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-01-2020, 12:19 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 Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet

Opening the attached spreadsheet will cause a UserForm to open. The purpose of the Userform is to ensure that the required label, markings and To and From addressee are selected.

Query 1 - How do I link the Userform checkboxes to their corresponding labels and markings (shapes on the Labels worksheet) and then copy the selected label and marking(s) to the places as indicated on the ToFrom worksheet? Only 1 label (if a label is required) would be selected at any one time.

Marking 3 is a mandatory marking and as such will be already 'prepositioned' onto the To/From worksheet.

Query 1a - If none of the label checkboxes are ticked but the Marking 1 checkbox was, how would you get it copied over to position itself where Label 1 is currently indicated?

Query 2 - Similar to the above but this time what is needed to copy over the To/From addresses to the appropriate place on the To/From worksheet?

Query 3 - the idea behind the 'create inner mailing labels' command button is that the workbook would be temporarily hidden until it is clicked on which would then cause the workbook to become visible so that the User can double-check the information is correct before committing the To/From worksheet to print.

I've sent the better part of the week googling for solutions and 'stealing' bits out of other workbooks that I have been assisted with but I'm beginning to think I'm not putting in the right search terms because I can't find what I am looking for.

Any assistance is, as always, greatly appreciated.

cheers


Mike
Attached Files
File Type: xltm Mailing Label for upload.xltm (36.9 KB, 7 views)
Reply With Quote
  #2  
Old 10-01-2020, 04:57 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

This looks like an assignment so I'm not going to do all the work for you but am happy to guide you on how to get this working.

I'm a big fan of setting up loops in user forms to allow your form design to be more modular and adapt without altering code if you need to add or remove controls. To do this, I normally use properties on a control such as the Tag property but perhaps in this case I would use the Caption. You can then align that text with your shapes and/or named ranges in the workbook. We can come back to this later but the following suggestions are all in line with setting yourself up for success. The better organised the workbook and userform, the more efficient you can make your code.

In terms of userform design, if your Labels are 'mutually exclusive' (meaning you can only choose one at a time), then you should use Option buttons instead of checkboxes. The benefits of using Option buttons over checkboxes are:
1. Interface 'unwritten rules' tell the user that options in a group are mutually exclusive but checkboxes are not. This is a visual clue on functionality for the user when they look at the userform.
2. You can assign the Group property of each of those option buttons to the same value and you then won't need to code any events to turn other values off when one is checked. This saves a bunch of coding. In this case, you should set the group property on the optionbuttons to "Label"

Your coding is going to be labour intensive partly because of the layout of your 'Inner envelope' sheet. Merged cells add zero value and further complicate this. Change this sheet to a two column table with 'Address n' in the first column and the actual address in the second column. Get rid of the merged cells. Name this table as "Addresses". This will make the coding easier when we get to it.

To give you a little coding preparation guidance, it appears that your intention is for Markings to either be visible or not, according to the checkboxes. To do this, start by tagging each of the shapes on your ToFrom sheet. I would tag the Alt Text's Title property on each shape. Do this by right clicking on the shape's border, choose Format Shape > Shape Options >Size & Properties (third option) > Alt Text > Title. Set these to "Marking 1", "Marking 2" and "Marking 3" respectively. This will give you a way of identifying these shapes with your code.

You can check your handiwork if you put this code on the one button you have. This code should give you some real clues as to how you can make use of the step I just asked you to do.
Code:
Private Sub Innerenvelope_Click()
  Dim aSheet As Worksheet, aShape As Shape
  Set aSheet = ActiveWorkbook.Sheets("ToFrom")
  For Each aShape In aSheet.Shapes
    Debug.Print aShape.Title, aShape.TextFrame2.TextRange.Text, aShape.Visible
  Next aShape
End Sub
Once you've done the above preparatory steps, upload the refreshed workbook and we can help with the next steps.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 10-01-2020, 10:04 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

Hi Andrew,

Firstly, thank you for taking the time to explain the concepts and reasoning behind your suggestions - it is much appreciated and builds on the little Excel VBA knowledge that I have.

I would like to be able to say that this was an assignment...but in this case it most definitely isn't. This is a 'proof of concept' for something at work and to 'put someone in their place' who swears it can't be done using Excel.

Yes - the labels are mutually exclusive. Nine times out of ten, there will always be one of the labels used. That tenth time, there won't be a need for a label but there will be a need for Marking 1 to be used and to be situated where the 'current' four 'label 1' are showing. Otherwise Marking 1 will sit underneath whatever the selected 'Label' is and as seen in the bottom part of the ToFrom worksheet.

Hopefully I have been able to complete all of the preparatory steps correctly. Guess the proof of the pudding will be in the attached updated workbook.
Attached Files
File Type: xltm Mailing Label with help.xltm (31.1 KB, 6 views)
Reply With Quote
  #4  
Old 10-01-2020, 11:22 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

Ahh, so you are doing this out of spite - that is something I can truly appreciate.

I've done this as simply as I can. I decided your listboxes were a waste of space so I turned them into comboboxes and used the initialise macro to populate a second column with the addresses at the same time. This makes it much faster to apply the address to the right shape on the page as we don't need to find the matching entry in the source table. You can hide the second column if it bothers you by setting the column widths. You could also return to the list boxes as the same functionality would work with them too.

Notice what I did with your dummy addresses - you want the full address in a single cell to make life easier. You can add to that list and because it is a table (list object), the range automatically expands to include the new rows.

The option and checkbox controls all have a Tag property - make use of that to specify the text which could differ from the caption.
Attached Files
File Type: xlsm Mailing Label with help2.xlsm (44.6 KB, 12 views)
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #5  
Old 10-01-2020, 11:57 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

LOVING your work! And with so very little code to boot!


Is it possible that when the command button is pressed that the UserForm disappears and just leaves the ToFrom open? Resolved when I realised I already had the appropriate piece of code in another userform.


For the time that there won't be a need for a label but there is a need for Marking 1 to be used, is it possible for Marking 1 to be placed where the current four 'labels' are showing?


Is it possible to have each of the three labels with different border outline colours and matching text colours?


And an assumption on my behalf that the 'Labels' worksheet is no longer needed?

Last edited by kiwimtnbkr; 10-02-2020 at 04:02 AM. Reason: resolved one of my questions
Reply With Quote
  #6  
Old 10-02-2020, 05:13 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 didn't think you needed the Labels sheet. It serves no purpose in anything I did with the code. This is the code I would use for the requests you made above.
Code:
Option Explicit

Private Sub Innerenvelope_Click()
  Dim aSheet As Worksheet, aShape As Shape, aCtl As Control, sLabel As String
  Dim bLabel As Boolean
  
  Set aSheet = ActiveWorkbook.Sheets("ToFrom")
  bLabel = False
  
  For Each aCtl In Me.frameLabel.Controls
    If aCtl = True Then
      SetLabels sLabel:=aCtl.Tag, aSheet:=aSheet
      bLabel = True
    End If
  Next aCtl
  
  For Each aCtl In Me.frameMarking.Controls
    For Each aShape In aSheet.Shapes
      If aShape.Title = aCtl.Tag Then
        aShape.Visible = aCtl
      End If
      If aShape.Title = "Marking 1" Then
        If bLabel Then
          aShape.Top = 489
        Else
          aShape.Top = 440
        End If
      End If
    Next aShape
  Next aCtl
  
  For Each aShape In aSheet.Shapes
    If aShape.Title = "To Address" Then
      aShape.TextFrame2.TextRange.Text = Me.cbTo.Column(1)
    ElseIf aShape.Title = "From Address" Then
      aShape.TextFrame2.TextRange.Text = Me.cbFrom.Column(1)
    End If
  Next aShape
  
  Me.Hide

End Sub

Private Sub UserForm_Initialize()
  Dim aCell As Range, aLO As ListObject, x As Integer, aRange As Range
  
  Set aLO = ActiveWorkbook.Sheets("Inner envelope").ListObjects("tblAddress")
  Set aRange = aLO.DataBodyRange
  Me.cbTo.List = aRange.Value
  Me.cbFrom.List = aRange.Value
End Sub

Sub SetLabels(sLabel As String, aSheet As Worksheet)
  Dim aCtl As Control, lngColour As Long, aShape As Shape
  Select Case sLabel
    Case "Warning":  lngColour = RGB(120, 0, 0)
    Case "Caution":  lngColour = RGB(0, 120, 0)
    Case Else:       lngColour = RGB(0, 0, 120)
  End Select
  
  For Each aShape In aSheet.Shapes
    If aShape.Title = "Label" Then
      aShape.TextFrame2.TextRange.Text = sLabel
      aShape.Line.ForeColor.RGB = lngColour
    End If
  Next aShape
  
End Sub
I would also add and Unload command to the macro that calls the userform. This stops the form remembering your previous entry if you reshow the form during the session.
Code:
Private Sub Workbook_Open()
    Application.Visible = True
    UserForm1.Show
    Unload UserForm1
    Sheets("ToFrom").Activate
End Sub
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #7  
Old 10-02-2020, 05:52 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

Must be getting close to a 100% solution - the text within the labels isn't changing to the matching border colour and is remaining black.

Have deleted to the Labels worksheet - thanks for the confirmation on that one

Last edited by kiwimtnbkr; 10-02-2020 at 02:07 PM.
Reply With Quote
  #8  
Old 10-04-2020, 01:11 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

The borders work on my machine so I'm not sure what is going on with your machine.

Perhaps a better alternative is the make three different labels with exactly the look you want and place them all in the same location overlapping each other. The code that hides the marking could then be adapted to hide the Labels in the same way so that no more than one is visible.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #9  
Old 10-04-2020, 01:22 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

the borders are definitely changing colour as intended - it's the text inside that isn't changing colour, it needs to be able to match the selected colour of the label border of the 3 individual labels ,for example if the selected labels border colour was yellow then the text needs to be yellow as well.

Thank you for assisting with this - it truly is appreciated!
Reply With Quote
  #10  
Old 10-04-2020, 05:39 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

The font colour can also be changed by adding this line
Code:
Sub SetLabels(sLabel As String, aSheet As Worksheet)
  Dim aCtl As Control, lngColour As Long, aShape As Shape
  Select Case sLabel
    Case "Warning":  lngColour = RGB(120, 0, 0)
    Case "Caution":  lngColour = RGB(0, 120, 0)
    Case Else:       lngColour = RGB(0, 0, 120)
  End Select
  
  For Each aShape In aSheet.Shapes
    If aShape.Title = "Label" Then
      aShape.TextFrame2.TextRange.Text = sLabel
      aShape.Line.ForeColor.RGB = lngColour
      aShape.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = lngColour
    End If
  Next aShape
  
End Sub
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #11  
Old 10-04-2020, 05:53 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

Oh I was so close with the code I googled and was trying to get the text colour to match Thank you for putting me out of my misery

Final piece of the jigsaw...

What is currently happening - if no labels are selected but Marking 1 is

see first attached image

Marking 1 is moving correctly but is getting hidden behind the previously selected label

see second attached image

Ideally what I would like to happen is if there are no labels selected but Marking 1 is then it appears in the following layout

see third attached image

I am more than happy that if a label and Marking 1 are selected that they appear as they currently do.

Last edited by kiwimtnbkr; 10-04-2020 at 05:57 PM. Reason: images attached
Reply With Quote
  #12  
Old 10-04-2020, 09:24 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

Perhaps the easiest solution is to add some code in the form's Initialize event to reset the default views on the worksheet.

You could also get that code to read the status of the worksheet to 'prefill' the userform settings to match the current status of the worksheet. This would avoid the need to reset everything when you only want to make a single change. It would probably require an extra button to clear an option though since it is not immediately apparent how to clear ALL options once one has been selected.

If you want to control the visual label positions WHILE the userform is showing then you need to add code to run as checkboxes and Option buttons are manipulated. At the moment all the magic happens AFTER you click the "Create inner..." button but you can move that out to all of the option/checkbox "on change" events if you want to micromanage it.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #13  
Old 10-05-2020, 12:20 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

Hi Andrew,

I understand the concept of what you're saying but I have no concept on how to even begin the coding for it... definitely way way above my level of understanding of VBA.

Thanks heaps for all the assistance you have provided me - it is absolutely appreciated. Just wish that I was able to catch up with you and shout you a couple of beers in appreciation!

cheers
Mike
Reply With Quote
  #14  
Old 10-05-2020, 03:29 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

It looks like you've created additional Marking 1 shapes in your workbook. The workbook I had only had one Marking 1 shape and your screen capture shows 4 of them now. The code I had created to move that one item is going to need to be adapted to deal with the extra 3 instances.

There can be a point where the coding of alternate layouts for multiple conditions becomes so complicated that it can be good to step back and consider alternatives. One such alternative is different worksheets for each layout option and your code starts by hiding sheets and only showing the preferred worksheet and then changes a little text or that 'active' sheet. I'm not sure you are at that point yet but it is worth considering if you intend to keep moving the goalposts as it opens up the layout options considerably.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #15  
Old 10-05-2020, 04:17 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

I did make comment in one of my earlier posts about the need for 4 instances of Marking 1 if no label was selected - lesson learnt for me to add pictures instead of a poorly worded explanation
Quote:
Yes - the labels are mutually exclusive. Nine times out of ten, there will always be one of the labels used. That tenth time, there won't be a need for a label but there will be a need for Marking 1 to be used and to be situated where the 'current' four 'label 1' are showing. Otherwise Marking 1 will sit underneath whatever the selected 'Label' is and as seen in the bottom part of the ToFrom worksheet.
This is one reason I like this forum - you guys that work with this stuff all the time can always see a better way of doing things that us mere 'users and tinkers' can't!

I like your idea of a second worksheet to cover that tenth time were there is no need for any Labels but there is a need for the four instances of Marking 1 and I am creating that worksheet now.

I'm going to hazard a guess that somewhere in the Userform code there will need to be code added to say that if there isn't a label selected but Markings 1 and 2 are, along with the addresses, then to use and display the second worksheet?

Thank you for your perseverance with this... I wish my brain was wired to understand the in's and out's of VBA - I have these bright ideas but not the knowledge to be able to execute them

Last edited by kiwimtnbkr; 10-05-2020 at 04:27 PM. Reason: grammar - lack there of!
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 08:14 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