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 inputbox
but have the other stakeholder remain the same