View Single Post
 
Old 10-01-2020, 04:57 PM
Guessed's Avatar
Guessed Guessed is offline Windows 10 Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,977
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