|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
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 Code:
Private Sub Workbook_Open() Application.Visible = True UserForm1.Show Unload UserForm1 Sheets("ToFrom").Activate End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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 |
#9
|
|||
|
|||
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! |
#10
|
||||
|
||||
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 |
#11
|
|||
|
|||
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 |
#12
|
||||
|
||||
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 |
#13
|
|||
|
|||
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 |
#14
|
||||
|
||||
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 |
#15
|
|||
|
|||
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:
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! |
|
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 |