Check boxes on a userform

[mummbles74]

I have created a template sheet that I will be using for my team to create quotation. I have generated the first part of the form that completes the pricing part.
I have a userform that I want to use to select different conditions to a quotation. I want the user to be able to tick/check the check boxes and for the text in the caption to be used to create a list that is then inserted into the sheet. I learn on the go all the time and it has been a while since I have used this level of code so I want to be able to work through 1 step at a time.

The first task i want to accomplish is creating the body of text (or number of filled rows) that is a result of the selected boxes. i am making the assumption that the captions might not be able to be used for this and I will have to generate the text on a separate sheet or in the code itself.

Thanks in anticipation.

 

[mummbles74]

All

Back to work on this form an I have been getting the functionality that I am looking for and all works brilliantly and seems to be really seamless. i have the form entering titles by offsetting from the last used cell etc which is great.
I have 2 of these userforms one for conditions and one for deliverable. Their are a couple of things that I would like to know if they can be done.

The first and main issue I have is:
Some of the check box captions are a few sentences long and when they are entered into the spread sheet (a merged cell) I can not get it to autofit I have tried both
"Range("B"&i).row.autofit"
"Activecell.row.autofit"
but neither of these seem to work

The second issue is it possible to create the checkbox captions from a list on a sheet?

Thank you all in advance again.

 

[mummbles74]

Just as a further update i have managed to adjust some code that i found on another page that seems to make the autofit for merged cells work

I will check how to upload code and post it just for anyones reference or so it can be simplified if needs be.

Just need to work on getting userform to insert a checkbox and add the caption from a list on a spreadsheet.

 

[BNR]

Hi,

How about the following?

This uses a defined range (which would be a list of display values on a defined worksheet) to build a collection of checkbox display values.
Then cycle through the collection and get each display value out (this where each checkbox would be created)

First build a collection of display values for each checkbox,

Public Function Build_List() As Collection

    Dim RnG As Range
    Dim This_Value As String
    Dim This_Cell As Range
   
    ' setup the collection
    Set Temp = New Collection

    ' set where the list starts
    Set RnG = ActiveSheet.Range("A2:A15")
   
    ' loop through the defined range
    For Each This_Cell In RnG
   
        ' grab the 1st disaplyed value
        This_Value = This_Cell.Value
       
        ' add to temp collection
        Temp.Add This_Value

    Next This_Cell
   
    ' return the built collection
    Set Build_List = Temp
   
End Function

Use this to cycle through the collection and build each checkbox.

Public Sub Build_Checkboxes()

    Dim ChKBoxes As Collection
    Dim Display As Variant
    Dim iRow As Double
   
    ' set a local collection based on the build_list function
    Set ChKBoxes = Build_List

    ' this is only used to iterate through collection and display on sheet
    iRow = 2

    ' iterate through the collection - use this output to build your checkboxes
    For Each Display In ChKBoxes
        ' display held value on the sheet (confirms output)
        ActiveSheet.Cells(iRow, 10).Value = CStr(Display)
        iRow = iRow + 1
   
    Next Display


End Sub

Hope this helps,
BenR

 

[mummbles74]

All, thank you for all your input, I have butchered merged, changed and modified all the code above plus some I have found elsewhere and have the template working pretty much exactly as required.

There is one final function that I am not sure if is even possible, I have tried a few different ways but as yet can not get it to function fully.

I would like to be able to have a save button that saves a native excel format as well as a PDF, with the file name based on cell values. I can achieve this when I preset the folder location but I want the user to be able to select the destination. is there anyway to make this work with code?

 

[BNR]

Hi,

A quick look around and I found this;

Get File Path (ends with folder)

I know how to let the user click on a button to navigate to a specific file to open. Code: Private Sub CommandButton2_Click() Dim vaFiles As Variant vaFiles = Application.GetOpenFilename...

stackoverflow.com

You can use the following code to allow the user to nominate a desired path;
It will return a string of the path OR a vbnullstring if action cancelled

Public Function FolderSelection() As String
   
    ' function will return selected path as a string
    ' is user cancels function, then function return vbnullstring - this allows for a test for valid length of function return
    Dim MyPath As String
   
    MyPath = SelectFolder("Select Folder", "")
   
    If Len(MyPath) Then
        MsgBox "Selected destination : " & Chr(10) & MyPath, vbInformation, "User nominated path"
        FolderSelection = MyPath
    Else
        MsgBox "User cancelled action"
        FolderSelection = vbNullString
    End If
   
End Function

This section requires that you add a reference to a library in the VBA editor.

In the VBA Editor's Tools menu, click References... scroll down to "Microsoft Shell Controls And Automation" and select it.

'Both arguements are optional. The first is the dialog caption and
'the second is is to specify the top-most visible folder in the
'hierarchy. The default is "My Computer."

Function SelectFolder(Optional Title As String, Optional TopFolder As String) As String
    Dim objShell As New Shell32.Shell
    Dim objFolder As Shell32.Folder

    'If you use 16384 instead of 1 on the next line, files are also displayed
    Set objFolder = objShell.BrowseForFolder(0, Title, 1, TopFolder)
    If Not objFolder Is Nothing Then SelectFolder = objFolder.Items.Item.Path
   
End Function

I hope this helps,
BenR