[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1066
  • Last Modified:

Excel userform checklist

Knowning absolutely nothing about excel i'm reaching out here.
we're looking for a simple checklist when installing a networked workstation/laptop. But would like to save the end result. So no paper which is forgoten, even lost afterwards. and so on.
We would like to save an excel worksheet on that central location, where all our install shortcuts are placed pointing to the software that needs to be installed. keeping all items together.
Having done some research on the web we have attached our 1st attempt. Just to get a feel where were are, or not :-)
So far the intro.

requirements in the checklist are;
computername, date, engineername, then a checklist of software. The catch is the list of software. Which is different for every customer. So we would like to have this list on a differrent worksheet within the same excel. which the engineer will follow when installing the machine. This way we can reuse the sheet an only adjusting the software list.
And for easy data entry we would like to use the userform option in excel, and then save the data into the excel sheet. Again, easy checklist. so the engineer should not select from a list of software, but is presented the list, and has to check(box) the application when he has installed it.
Userform2.xlsm
0
phylaxict
Asked:
phylaxict
  • 3
  • 3
1 Solution
 
David Johnson, CD, MVPOwnerCommented:
It would be better to use another product i.e. Microsoft Deployment Toolkit and automate it from install to start to end of giving it to the user.
0
 
byundtCommented:
It wasn't clear what you wanted the code to do. I assumed the following:
1.  User runs macro to load the userform
2.  All the possible programs are shown on the userform in a Combobox with multi-select enabled and options displayed.
3.  In addition to entering the name of the computer, date and engineer--the user must select which programs need to be installed. They do so by checking the box to left of the program in the Combobox.
4.  If user presses the Invoeren button, the computer name, date and engineer are entered on the next line in worksheet Blad1. In addition, the selected programs are listed in columns to the right of those items, each with a checkbox.
5.  When the engineer sets up the computer, he checks the box on worksheet Blad1 after each program is loaded.
Private Sub btn_Invoeren_Click()
Dim LastRow As Object

          Set LastRow = Blad1.Range("a65536").End(xlUp)
          
          If Not IsDate(Me.txt_datum) Then
                    MsgBox "Please enter a valid date"
                    Exit Sub
          End If

          LastRow.Offset(1, 0).Value = txt_computernaam.Text
          LastRow.Offset(1, 1).Value = txt_datum.Text
          LastRow.Offset(1, 2).Value = txt_engineer.Text
          CreateCheckboxes LastRow.Offset(1, 0).EntireRow
                
          response = MsgBox("Do you want to enter another record?", _
              vbYesNo)

          If response = vbYes Then
              txt_computernaam.Text = ""
              txt_datum.Text = ""
              txt_engineer.Text = ""
              ClearProgramSelections

              txt_computernaam.SetFocus

          Else
              Unload Me
          End If

      End Sub


Private Sub btn_Einde_Click()
          End
End Sub

Private Sub CreateCheckboxes(rg As Range)
Dim i As Long, j As Long, n As Long, numColumns As Long
Dim cel As Range
Dim ws As Worksheet
Dim sCaption As String
Set ws = rg.Worksheet
numColumns = 2      'Each checkbox occupies this number of columns
n = Me.ListBox1.ListCount
For i = 1 To n
    If Me.ListBox1.Selected(i - 1) Then
        sCaption = Me.ListBox1.List(i - 1)
        Set cel = rg.Cells(1, j * numColumns + 4)
        j = j + 1
        With ws.CheckBoxes.Add(cel.Left, cel.Top - 1, cel.Width * numColumns, cel.Height - 1)
            .Caption = sCaption
            .LinkedCell = cel.Address
            .Name = "cb" & cel.Address(False, False)        'Name it like "cbA11"
        End With
        cel.NumberFormat = ";;;"                       'Hide the linked TRUE/FALSE value
    End If
Next
End Sub

Private Sub ClearProgramSelections()
Dim i As Long, n As Long
With Me.ListBox1
    n = .ListCount
    For i = 1 To n
        .Selected(i - 1) = False
    Next
End With
End Sub

Open in new window

ComputerSetupChecklistQ28489456.xlsm
0
 
phylaxictAuthor Commented:
@David Johnson i know, but we want to start simpel. and have not invested time (or money) in that product. but right youare, when one should automate repetative tasks

@byundt right on the money!!!
just 2 things. I get an error when entering the data: compile error, variable not defined. on the respons=msgbox
and when i ad an application it does not show up in the list
but otherwise en beauty
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
byundtCommented:
I added a variable declaration to solve the compile error, and a userform_Initialize sub to let you add applications to the list.
Private Sub btn_Invoeren_Click()
Dim LastRow As Object
Dim response As Integer

          Set LastRow = Blad1.Range("a65536").End(xlUp)
          
          If Not IsDate(Me.txt_datum) Then
                    MsgBox "Please enter a valid date"
                    Exit Sub
          End If

          LastRow.Offset(1, 0).Value = txt_computernaam.Text
          LastRow.Offset(1, 1).Value = txt_datum.Text
          LastRow.Offset(1, 2).Value = txt_engineer.Text
          CreateCheckboxes LastRow.Offset(1, 0).EntireRow
                
          response = MsgBox("Do you want to enter another record?", _
              vbYesNo)

          If response = vbYes Then
              txt_computernaam.Text = ""
              txt_datum.Text = ""
              txt_engineer.Text = ""
              ClearProgramSelections

              txt_computernaam.SetFocus

          Else
              Me.Hide
              Unload Me
          End If

      End Sub


Private Sub btn_Einde_Click()
          Me.Hide
          Unload Me
End Sub

Private Sub CreateCheckboxes(rg As Range)
Dim i As Long, j As Long, n As Long, numColumns As Long
Dim cel As Range
Dim ws As Worksheet
Dim sCaption As String
Set ws = rg.Worksheet
numColumns = 2      'Each checkbox occupies this number of columns
n = Me.ListBox1.ListCount
For i = 1 To n
    If Me.ListBox1.Selected(i - 1) Then
        sCaption = Me.ListBox1.List(i - 1)
        Set cel = rg.Cells(1, j * numColumns + 4)
        j = j + 1
        With ws.CheckBoxes.Add(cel.Left, cel.Top - 1, cel.Width * numColumns, cel.Height - 1)
            .Caption = sCaption
            .LinkedCell = cel.Address
            .Name = "cb" & cel.Address(False, False)        'Name it like "cbA11"
        End With
        cel.NumberFormat = ";;;"                       'Hide the linked TRUE/FALSE value
    End If
Next
End Sub

Private Sub ClearProgramSelections()
Dim i As Long, n As Long
With Me.ListBox1
    n = .ListCount
    For i = 1 To n
        .Selected(i - 1) = False
    Next
End With
End Sub

Private Sub UserForm_Initialize()
Dim rg As Range
With Worksheets("Blad2")
    Set rg = .Range("A1")       'First program
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))    'All the programs
End With
Me.ListBox1.RowSource = "'" & rg.Worksheet.Name & "'!" & rg.Address
End Sub

Open in new window

ComputerSetupChecklistQ28489456.xlsm
0
 
phylaxictAuthor Commented:
WOW sweet! tnx

now comes the part where I become a pain in the a... :-)
would it be possible not to copy the selected app with tick boxes, cause you cannot do anything with it. like a filter. just copy the name of the selected app into the cells next to computer name, engineer etc??

so we keep the tick option in the userform, but copy only the app name?
0
 
byundtCommented:
When I first read your question, I assumed that setting up computers was a two part process. In the first step, you wrote the work order using the checkboxes on the userform. In the second step, someone (perhaps a different person) documented that it was done completely using the checkboxes on the worksheet.

If you don't want checkboxes on the worksheet, then you need to replace the statements that create the checkboxes with one that just copies over the name of the application. If you don't want a blank column between the name of each application, then change the value of numColumns to 1 in the commented statement. All of these changes occur in the following sub:
Private Sub CreateCheckboxes(rg As Range)
Dim i As Long, j As Long, n As Long, numColumns As Long
Dim cel As Range
Dim ws As Worksheet
Dim sCaption As String
Set ws = rg.Worksheet
numColumns = 2      'Each checkbox occupies this number of columns
n = Me.ListBox1.ListCount
For i = 1 To n
    If Me.ListBox1.Selected(i - 1) Then
        sCaption = Me.ListBox1.List(i - 1)
        Set cel = rg.Cells(1, j * numColumns + 4)
        j = j + 1
        'With ws.CheckBoxes.Add(cel.Left, cel.Top - 1, cel.Width * numColumns, cel.Height - 1)
            '.Caption = sCaption
            '.LinkedCell = cel.Address
            '.Name = "cb" & cel.Address(False, False)        'Name it like "cbA11"
        'End With
        'cel.NumberFormat = ";;;"                       'Hide the linked TRUE/FALSE value
        cel.Value = sCaption
    End If
Next
End Sub

Open in new window

ComputerSetupChecklistQ28489456.xlsm
0
 
phylaxictAuthor Commented:
Just what I was looking for. and fast response as well.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now