Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have the following code which populates info to the spreadsheet once the addbutton is clicked from the userform:

Sub addbutton_Click()
MsgBox "Directorate has been added", vbOKOnly
TransferMasterValue

Sub TransferMasterValue()
Dim allchecks As String
Dim ws1 As Worksheet
Dim emptyrowlong As Long

allchecks = GetCheckBoxes()

If Len(allchecks) > 0 Then
'Your code to transfer
Set ws1 = Sheets("Master")

'emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
emptyrow = ws1.Cells(Rows.Count, 1).End(xlUp).Row + 1    '<<< safer....
emptyrowlong = emptyrow
With ws1
        .Cells(emptyrow, xLastName).Value = surname.Value
        .Cells(emptyrow, xFirstName).Value = firstname.Value
        .Cells(emptyrow, xTitle).Value = tod.Value
        .Cells(emptyrow, xBranch).Value = branch.Value
        .Cells(emptyrow, xProgramAreas).Value = program.Value
        .Cells(emptyrow, xEmail).Value = email.Value
        .Cells(emptyrow, xStakeholder).Value = allchecks
        .Cells(emptyrow, xofficephone).Value = officenumber.Value
        .Cells(emptyrow, xcellphone).Value = cellnumber.Value

        If PACT.Value = True Then
        Worksheets("Master").Cells(emptyrowlong, xPACT).Value = "1"
        Else
        Worksheets("Master").Cells(emptyrowlong, xPACT).Value = ""
        End If
        If PrinceRupert.Value = True Then
        Worksheets("Master").Cells(emptyrowlong, xPrinceRupert).Value = "1"
        Else
        Worksheets("Master").Cells(emptyrowlong, xPrinceRupert).Value = ""
        End If
        If WPM.Value = True Then
        Worksheets("Master").Cells(emptyrowlong, xWPM).Value = "1"
        Else
        Worksheets("Master").Cells(emptyrowlong, xWPM).Value = ""
        End If
        If Montreal.Value = True Then
        Worksheets("Master").Cells(emptyrowlong, xMontreal).Value = "1"
        Else
        Worksheets("Master").Cells(emptyrowlong, xMontreal).Value = ""
        End If
        If TET.Value = True Then
        Worksheets("Master").Cells(emptyrowlong, xTET).Value = "1"
        Else
        Worksheets("Master").Cells(emptyrowlong, xTET).Value = ""
        End If
        If US.Value = True Then
        Worksheets("Master").Cells(emptyrowlong, xUS).Value = "1"
        Else
        Worksheets("Master").Cells(emptyrowlong, xUS).Value = ""
        End If
        If TC.Value = True Then
        Worksheets("Master").Cells(emptyrowlong, xTC).Value = "1"
        Else
        Worksheets("Master").Cells(emptyrowlong, xTC).Value = ""
        End If
        If Other.Value = True Then
        Worksheets("Master").Cells(emptyrowlong, xOther).Value = "1"
        Else
        Worksheets("Master").Cells(emptyrowlong, xOther).Value = ""
        End If
     Set Rng = .Range("A" & .[A65546].End(xlUp).Row)
End With
Rng.Offset(1, 0).EntireRow.Insert shift:=xlDown

Else
    Set ws1 = Sheets("Master")

'emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
emptyrow = ws1.Cells(Rows.Count, 1).End(xlUp).Row + 1    '<<< safer....

With ws1
        .Cells(emptyrow, xLastName).Value = surname.Value
        .Cells(emptyrow, xFirstName).Value = firstname.Value
        .Cells(emptyrow, xTitle).Value = tod.Value
        .Cells(emptyrow, xBranch).Value = branch.Value
        .Cells(emptyrow, xProgramAreas).Value = program.Value
        .Cells(emptyrow, xEmail).Value = email.Value
        .Cells(emptyrow, xStakeholder).Value = allchecks
        .Cells(emptyrow, xofficephone).Value = officenumber.Value
        .Cells(emptyrow, xcellphone).Value = cellnumber.Value
Set Rng = .Range("A" & .[A65546].End(xlUp).Row)
End With
Rng.Offset(1, 0).EntireRow.Insert shift:=xlDown

End If
End Sub

' Populate checkboxes from space-separated values in strList.
' Pass "" to just clear checkboxes
Sub SetCheckBoxes(strList As String)
Dim arr, i As Long, arrCb, tmp

PACT.Value = False
PrinceRupert.Value = False
WPM.Value = False
Montreal.Value = False
TET.Value = False
TC.Value = False
US.Value = False
Other.Value = False

arrCb = WhatCheckboxes()

If Len(strList) > 0 Then
    arr = Split(strList, " ")
    For i = LBound(arr) To UBound(arr)
        tmp = Trim(arr(i))
        If Not IsError(Application.Match(tmp, arrCb, 0)) Then
            Me.Controls(tmp).Value = True
        End If
    Next i
End If

End Sub

'Get the checked checkboxes as a space-separated string
Function GetCheckBoxes() As String
Dim arrCb, i As Long, rv As String

 arrCb = WhatCheckboxes()
 rv = ""

 For i = LBound(arrCb) To UBound(arrCb)
    If Me.Controls(arrCb(i)).Value = True Then
        rv = rv & IIf(Len(rv) > 0, " ", "") & arrCb(i)
    End If
 Next i
 GetCheckBoxes = rv
End Function

'returns the name of all Stakeholder checkboxes
Function WhatCheckboxes()
WhatCheckboxes = Array("PACT", "PrinceRupert", "WPM", _
                      "Montreal", "TET", "TC", "US", "Other")
End Function

what i want to achieve is that everytime the other checkbox is clicked, then it would populate an inputbox and the user would specify which other stakeholder the person belongs to and this would replace just the other portion of the .cells(emptyrow, xstakeholder).value

essentially, the code above populates names of checkboxes with spaces in between depending on which checkboxes are clicked. If, PACT is clicked then it populates the name PACT at xStakeholder column. if PACT, PrinceRupert, TC, US, Other is clicked then it would populate as such: PACT PrinceRupert TC US Other in xstakeholder column. i want to replace just the other portion with whatever the user inputs in the inputboxbut have the other stakeholder remain the same

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.