#1
|
|||
|
|||
Falling at the last hurdle with Checkboxes and Option buttons
I am falling at the last hurdle and have spent the last two days trying to find an answer myself and feels like I have tried a million different lines of code without any success.
I have attached the in question troublesome spreadsheet template in the hopes it allows for a successful solution. Upon opening the file, it is set up to hid the Excel application and draw the users attention to the Notes UserForm that pops up. So far so good. And I apologise now for the use of SENDKEYS to turn NUMLOCK back on so if anyone knows a better way the enforce NUMLOCK on please feel free to adjust what you need to. The idea behind the use is to force the dropdown lists to appear without the User clicking on them. My intent is that the user will read the notes first and then 'tick' the Check Box to 'agree' that they have done so. Next step is they select the 'Section' Option Button to designate what Section that belong to. This will then cause the Continue Command button to become active. The idea behind the user selecting what Section they belong to is that it will 'unhide' the appropriate worksheet for that Section. So far so good and I am happy with that - well sort of.... The issue starts if the User selects what Section they belong to first. This causes the Continue Command button to become active. What I need the UserForm to do is not let the Continue button become active until they have 'ticked' the Check box to agree they have read the notes and also selected what Section they being to - regardless of what order they ultimately do it in. The second issue is if the User selects the wrong Section and then tries to select the right Section and it causes the macro to error with a 'Run-time error '1004': Unable to set the Visible property of the Sheets class'. The workbook isn't protected and this one REALLY has me stuck. And if anyone is really bored and wants something to do for a rainy day type of thing - please feel free to tidy up my poor coding efforts. There will be better ways of getting what I need done but I have just pieced together what I have been able to find online into something that works. Many thanks Mike |
#2
|
|||
|
|||
Quote:
Disable the option buttons until the check box is 'ticked'. Quote:
Rather than making one of the three sheets visible, you could make them all visible then hide the two that aren't required. Maybe the attached will help. |
#3
|
|||
|
|||
FANtastic! Your suggested code changes are pretty much exactly what I looking for - thank you so much!
If you're able to further indulge me I would really appreciate it if you could assist me with just working this bit - I didn't think this bit thru properly when I attached the original spreadsheet late last night. In the spreadsheet I previously attached the sheet names were as follows: Sheet2 (Section 2) Sheet3 (Section 1) Sheet4 (Section 3) what if in the production spreadsheet the sheet names were something similar to these: Sheet2 (Report template - Advanced) Sheet3 (Report template - NextGen) Sheet4 (Report template - All Future) and the Option buttons in the UserForm were these caption names: OptionButton1 (NextGen) OptionButton2 (Advanced) OptionButton3 (All Future) |
#4
|
|||
|
|||
I guess that means Private Sub OptionButton1_Change() is now Private Sub NextGen_Change(), etc.
That negates use of the For i = 1 to 3 loops to establish sheet and Optionbutton names. In place of the loops write 3 lines for sheets and 3 lines for optionbuttons each using the actual name. Also change the sheet names accordingly elsewhere within the code. |
#5
|
||||
|
||||
Quote:
Quote:
Quote:
Quote:
|
#6
|
|||
|
|||
Quote:
although I don't think VBA accepts a control name with a space in it. Code:
Private Sub NextGen_Change() If NextGen.Value = True Then ' this replaces the loop Me.Controls(NextGen).BackColor = RGB(244, 244, 244) Me.Controls(Advanced).BackColor = RGB(244, 244, 244) Me.Controls(All Future).BackColor = RGB(244, 244, 244) Sheets("Report template - NextGen").Visible = True Sheets("Report template - Advanced").Visible = True Sheets("Report template - All Future").Visible = True ' Sheets(Array("Report template - Advanced", "Report template - All Future")).Visible = False Sheets("Report template - NextGen").Activate Range("H4").Select SendKeys "%{down}", True NextGen.BackColor = RGB(128, 255, 128) cmdContinue.Enabled = True End If End Sub |
#7
|
|||
|
|||
oh dear! I must be under the weather... so obvious that when I saw your reply I rolled my eyes as my own stupidity!
After sorting out a couple of minor errors of my own doing, I am stoked to report that the UserForm now does exactly what I need it to do and with the carryover into the spreadsheet so THANK YOU for your perseverance and tolerance for my lack of VBA skills. But (there is always a but!) I have now run into another issue that I am struggling with. Each of the individual Report template sheets, as you would have seen, have a command button to save the Report template as an .xlsm file. Part of the macro also copies information contained in two cells and uses that info as the majority of the file name. It also looks for "/", " / " and replaces it with "_" if they appear in either of the two cells so as not to muck up the file name and require the User to change them manually. Query 1 - is there a way to have the macro read a "/" in either of the two referenced cells and convert it to a "_" in the file name but leave it as "/" in the cell? The code for the command button, and different where necessary according to the worksheet names, is: Code:
Private Sub CommandButton1_Click() Sheet2.CommandButton1.BackColor = RGB(242, 242, 242) Dim fName As Variant Dim myCell As Range Dim myCharacterToReplace As String Dim myReplacementCharacter As String Set myCell = ThisWorkbook.Worksheets("Report template - Advanced").Range("L5") myCharacterToReplace = " / " myReplacementCharacter = "_" myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace _ , Replace:=myReplacementCharacter) Set myCell = ThisWorkbook.Worksheets("Report template - Advanced").Range("L5") myCharacterToReplace = "/" myReplacementCharacter = "_" myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace _ , Replace:=myReplacementCharacter) Set myCell = ThisWorkbook.Worksheets("Report template - Advanced").Range("H17") myCharacterToReplace = "/" myReplacementCharacter = "_" myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace _ , Replace:=myReplacementCharacter) fName = Application.GetSaveAsFilename(InitialFileName:=Range("H17").Value & " Report" _ & Range("A1").Value & Range("L5").Value, FileFilter:="Excel Files (*.xlsm), *.xlsm" _ , Title:="Save As COMSEC Incident report as") If fName = False Then Exit Sub ActiveWorkbook.SaveAs filename:=fName, FileFormat:=52 End Sub Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim xFileName As String Dim myCell As Range Dim myCharacterToReplace As String Dim myReplacementCharacter As String Set myCell = ThisWorkbook.Worksheets("Report template").Range("L5") myCharacterToReplace = " / " myReplacementCharacter = "_" myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace, Replace:=myReplacementCharacter) Set myCell = ThisWorkbook.Worksheets("Report template").Range("L5") myCharacterToReplace = "/" myReplacementCharacter = "_" myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace, Replace:=myReplacementCharacter) Set myCell = ThisWorkbook.Worksheets("Report template").Range("H17") myCharacterToReplace = "/" myReplacementCharacter = "_" myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace, Replace:=myReplacementCharacter) If SaveAsUI <> False Then Cancel = True xFileName = Application.GetSaveAsFilename(InitialFileName:=Range("H17").Value & Range("A1").Value & Range("L5") _ , FileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Save Report as") If xFileName <> "False" Then Application.EnableEvents = False ActiveWorkbook.SaveAs filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.EnableEvents = True End If End If End Sub I also tried referencing the three worksheets as an Array but that didn't work either. Query 2 - how, what, where have I gone wrong in the save routine.... and apologises for the longwinded ramble. Last edited by kiwimtnbkr; 06-10-2019 at 02:52 AM. Reason: clarified Save UI Cancel button happenings |
#8
|
|||
|
|||
Quote:
Something like Code:
' L5part = Sheets("Report template - Advanced").Range("L5").Value L5part = Replace(Replace(L5part, " / ", "/"), "/", "_") MsgBox L5part ' Quote:
|
#9
|
|||
|
|||
Got this one sorted out myself after a sleep and opening a fresh Excel session, which caused a bit of a light bulb moment. It helps when making templates with macros to actually close Excel, and then reopen the template in a clean Excel session so that the template acts as a template.
I have spend the last couple of hours looking and trying to get your code suggestion to work and buried myself in Google searches... I must be missing something obvious because I just can't get it to run.... HELP PLEASE, it IS the last piece of this jigsaw for me |
#10
|
|||
|
|||
Your CommandButton1_Click sub using variables for L5 and H17
Code:
Private Sub CommandButton1_Click() Sheet2.CommandButton1.BackColor = RGB(242, 242, 242) Dim fName As Variant Dim L5part As String, H17part As String L5part = ThisWorkbook.Worksheets("Report template - Advanced").Range("L5").Value L5part = Replace(Replace(L5part, " / ", "/"), "/", "_") 'MsgBox L5part 'just to see what the result is H17part = ThisWorkbook.Worksheets("Report template - Advanced").Range("H17").Value H17part = Replace(H17part, "/", "_") 'MsgBox H17part 'just to see what the result is fName = Application.GetSaveAsFilename(InitialFileName:=H17part & " Report" & Range("A1").Value _ & L5part, FileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Save As COMSEC Incident report as") If fName = False Then Exit Sub ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=52 End Sub |
#11
|
|||
|
|||
You Sir, are an absolute legend! That bit of code has SO nailed it on the sheet I tried it on.
I'll just finish putting it thru the entire workbook and if it works as I fully expect it to, then I will mark this as SOLVED. Rep has been left. Do you have a Paypal account that I can put a donation thru as thanks for your patience and perseverance? Many thanks Mike |
#12
|
|||
|
|||
Thank You is all I need.
I'm just an old retired guy that plays with this stuff for entertainment. Glad I was able to assist you along the way. |
#13
|
|||
|
|||
Got the individual worksheets going bang on and I thought it would also be a case of just transferring the code over to the 'ThisWorkbook' and away I would go to RESOLVED but as we have already discovered, I am not as clever as I would like to think I am...
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim xFileName As String Dim L5part As String, H17part As String 'L5part = ThisWorkbook.Worksheets("Report template - Advanced").Range("L5").Value 'L5part = Replace(Replace(L5part, " / ", "/"), "/", "_") 'H17part = ThisWorkbook.Worksheets("Report template - Advanced").Range("H17").Value 'H17part = Replace(H17part, "/", "_") L5part = ThisWorkbook.Worksheets("Report template - NextGen").Range("L5").Value L5part = Replace(Replace(L5part, " / ", "/"), "/", "_") H17part = ThisWorkbook.Worksheets("Report template - NextGen").Range("H17").Value H17part = Replace(H17part, "/", "_") 'L5part = ThisWorkbook.Worksheets("Report template - All Future").Range("L5").Value 'L5part = Replace(Replace(L5part, " / ", "/"), "/", "_") 'H17part = ThisWorkbook.Worksheets("Report template - All Future").Range("H17").Value 'H17part = Replace(H17part, "/", "_") If SaveAsUI <> False Then Cancel = True xFileName = Application.GetSaveAsFilename(H17part & " Report" & Range("A1").Value & L5part, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save report as") If xFileName <> "False" Then Application.EnableEvents = False ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.EnableEvents = True End If End If End Sub Last edited by kiwimtnbkr; 06-11-2019 at 01:54 AM. Reason: routine failure clarification |
#14
|
|||
|
|||
You're not telling it which one of the three sheets to use.
Only one of those sheets should be visible and that's the one you want. Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim xFileName As String Dim L5part As String, H17part As String Dim ws As Worksheet If ThisWorkbook.Worksheets("Report template - Advanced").Visible = True Then Set ws = ThisWorkbook.Worksheets("Report template - Advanced") ElseIf ThisWorkbook.Worksheets("Report template - NextGen").Visible = True Then Set ws = ThisWorkbook.Worksheets("Report template - NextGen") Else Set ws = ThisWorkbook.Worksheets("Report template - All Future") End If L5part = ws.Range("L5").Value L5part = Replace(Replace(L5part, " / ", "/"), "/", "_") H17part = ws.Range("H17").Value H17part = Replace(H17part, "/", "_") If SaveAsUI <> False Then Cancel = True xFileName = Application.GetSaveAsFilename(H17part & " Report" & ws.Range("A1").Value & L5part, _ "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save report as") If xFileName <> "False" Then Application.EnableEvents = False ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.EnableEvents = True End If End If End Sub |
#15
|
|||
|
|||
THAT absolutely gets two from me and also a BIG.GREEN.RESOLVED placed on it!
One thing I have enjoyed about this, is the reading I have been doing for how the code you have provided works - the use of ElseIf and Else - the use of and difference between cellrefpart = Replace(Replace and cellrefpart = Replace was very clever and very subtle. I feel that if I had to expand this workbook to include other sections that I would be able to now without too many issues and any errors generated would likely be typos on my behalf - like typing in the last lot of code and typing in 'Dim ws as Workbook' instead of 'Dim ws as Worksheet' and then going HUH! when it errored until I discovered my typo. MANY MANY thanks for ALL the assistance that you have provided me with in getting this spreadsheet project up and running how I had envisioned it. Mike |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Option Buttons - linking and control | natelfo | Visio | 8 | 01-25-2024 02:01 AM |
Option buttons for multiple choice questions | ScottishPolarBear | Word | 4 | 12-02-2014 02:23 PM |
Display cells dependent on option buttons | inq80 | Excel | 0 | 09-01-2014 03:40 PM |
How do you achieve Falling letters? | PPT | PowerPoint | 0 | 04-21-2012 06:12 AM |
Word 2007 Option buttons - transparency | JacobThomas | Word VBA | 1 | 11-02-2011 05:40 AM |