Post content from a textbox to sheet only if it is filled

M

MakayoExcel

New Member
Joined
Dec 2, 2021
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
I have a combobox called caller type with two options in there Managed Account Number or Account Rep.

if the Combobox is set to Managed account number is selected then all the text boxes that correspond to the rep i.e Txt_Repid,Txt_RepName, Txt_RepCompany Txt_RepAuthLevel will become greyed out and visibility=false.

if the Combobox is set to AccountRep is selected then all the text boxes that correspond to the rep i.e Txt_Repid,Txt_RepName, Txt_RepCompany Txt_RepAuthLevel will become visible.true and all these text boxes get written to a main textbox called Txt_CallInfo.

If the textbox is blank i do not want to write it when it is being written to the main textbox, i only want the textbox that have content text into to be written to the main textbox Txt_CallInfo

if the Combobox is set the Managed account number Then i do not want to write any of these textboxes Txt_Repid,Txt_RepName, Txt_RepCompany Txt_RepAuthLevel to be copied into main textbox -

if combobox is set to AccountRep but if there is no text in one or any of the textboxes (Txt_Repid,Txt_RepName, Txt_RepCompany Txt_RepAuthLevel) then i do not want to write it to the main textbox Txt_CallInfo,
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Not sure if this will help:


VBA Code: 
If Me.myCombo = "Managed account number" Then
  With Me
   .Txt_Repid.Visible = False
   .Txt_RepName.Visible = False
   .Txt_RepCompany.Visible = False
   .Txt_RepAuthLevel.Visible = False
  End With
End If

You could repeat that for other combo selections, or even consolidate that code IF there will only ever be 2 combo choices. Plus, I've no idea if the combo is bound and if so, which column, so if there is more than one column and the bound column is not the first column, you'll need the column reference, as in Me.myCombo.Column(x) where you provide the value for x. The column property is zero based by the way.

There's no point in setting the background color of an invisible combo.

Adding multiple field values to one textbox is not a good idea IMO but your code could simply concatenate each field into one string, which you then make the target field equal to that. If a control has no value, nothing will be concatenated. Your issue there might be that you also want separators such as commas or semicolons so may have to deal with that. You only choose to concatenate based on what the combo value (remember, might neet to be the column value) and the IF block of code that tests that will decide whether or not to concatenate.

I'm guessing that you'd use the combo after update event, but not enough post info to say for sure.
 
Upvote 0
Micron said:
Not sure if this will help:


VBA Code: 
If Me.myCombo = "Managed account number" Then
  With Me
   .Txt_Repid.Visible = False
   .Txt_RepName.Visible = False
   .Txt_RepCompany.Visible = False
   .Txt_RepAuthLevel.Visible = False
  End With
End If

You could repeat that for other combo selections, or even consolidate that code IF there will only ever be 2 combo choices. Plus, I've no idea if the combo is bound and if so, which column, so if there is more than one column and the bound column is not the first column, you'll need the column reference, as in Me.myCombo.Column(x) where you provide the value for x. The column property is zero based by the way.

There's no point in setting the background color of an invisible combo.

Adding multiple field values to one textbox is not a good idea IMO but your code could simply concatenate each field into one string, which you then make the target field equal to that. If a control has no value, nothing will be concatenated. Your issue there might be that you also want separators such as commas or semicolons so may have to deal with that. You only choose to concatenate based on what the combo value (remember, might neet to be the column value) and the IF block of code that tests that will decide whether or not to concatenate.

I'm guessing that you'd use the combo after update event, but not enough post info to say for sure.
Click to expand...
Yes thanks a lot some of the code for the project is seen below

Option Explicit


Private Sub CB_Reset_Click()

Application.ScreenUpdating = False
Unload Me
HCCSH_UF.Show
Application.ScreenUpdating = True

End Sub

Private Sub Userform_Initialize()

' to write prefilled text for textboxes
'Lbl_AgentName.Caption = Sheets("Agent & Team Info").Range("B1").Value 'Agents Name
'Lbl_AgentID.Caption = Sheets("Agent & Team Info").Range("B2").Value 'Agents ID
Txt_HCCSHCallStartTime.Value = Format(Now(), "mmmm/dd/yyyy - hh:mm:ss") 'Call start time
Txt_POC.MultiLine = True 'to allow multiple line in Purpose of call textbox
Txt_POC.Value = Format(Txt_POC.Value, "General")
Txt_POC.Value = "Reason for the call: TP xxxxxxxxxxxxx" 'prefilled text for purpose of call
'Txt_SecQue.MultiLine = True 'to allow multiple line in Purpose of call textbox
'Txt_SecQue.Value = Format(Txt_SecQue.Value, "General")
'Txt_SecQue.Value = " Answered correctly: < question > < question > " & vbNewLine & " Answered correctly: < question > < question >"
Txt_CallNotes.MultiLine = True
Txt_CallNotes.Value = Format(Txt_CallNotes.Value, "General")
Txt_TSADiNP.MultiLine = True
Txt_TSADiNP.Value = Format(Txt_CallNotes.Value, "General")
Txt_ELCSNPTemp.MultiLine = True
Txt_ELCSNPTemp.Value = Format(Txt_ELCSNPTemp.Value, "General")
Txt_SecQue1.Value = "< Question >"
Txt_SecQue1.Value = Format(Txt_SecQue1.Value, "General")
Txt_SecQue2.Value = "< Question >"
Txt_SecQue2.Value = Format(Txt_SecQue2.Value, "General")
Txt_SecQue3.Value = "< Question >"
Txt_SecQue3.Value = Format(Txt_SecQue3.Value, "General")
Txt_SecQue4.Value = "< Question >"
Txt_SecQue4.Value = Format(Txt_SecQue4.Value, "General")
MP_CallInfo.Pages("Pg_Rep").Visible = False
Txt_SecLevMea.Enabled = False
Txt_SecLevMea.BackColor = vbScrollBars


' to click if the purpose of the call is account specific
If TB_AccountSpecific.Value = False Then
CB_CallerType.Enabled = False
CB_CallerType.BackColor = vbScrollBars
Txt_SecLevMea.Enabled = False
Txt_SecLevMea.BackColor = vbScrollBars
MP_CallNotes.Visible = False
MP_CallInfo.Pages("Pg_Rep").Visible = False
MP_SecQue.Pages("Pg_SecQue2").Visible = False

End If



End Sub
Private Sub Txt_POC_AfterUpdate()
Dim POCAlert As String
Dim LstCTr As Range
Dim LstTrnd As Range

Set LstTrnd = ThisWorkbook.Worksheets("Lists").Range("List_Trained")
Set LstCTr = ThisWorkbook.Worksheets("Lists").Range("List_CallTier")

POCAlert = MsgBox("Are you Trained on this request Tier? ", vbQuestion + vbYesNoCancel, "Call Tier Check")

If POCAlert = vbYes Then
CB_CallTier.Value = "Trained Teirs"
CB_CallTier.RowSource = LstTrnd.Address(External:=True)
ElseIf POCAlert = vbNo Then
TierSecurity_UF.Show
CB_CallTier.Value = "Pick From Below"
Me.CB_CallTier.RowSource = LstCTr.Address(External:=True)
End If
End Sub

Private Sub TB_AccountSpecific_Change()

'to enable the input fields if it is account specific
If TB_AccountSpecific.Value = True Then
TB_AccountSpecific.Caption = "Yes it is!"
CB_CallerType.Enabled = True
CB_CallerType.BackColor = vbWindowBackground
'Txt_SecLevMea.Enabled = False
Txt_SecLevMea.BackColor = vbWindowBackground
MP_CallInfo.Visible = True
MP_CallNotes.Visible = True

Else
TB_AccountSpecific.Caption = "No its not!"
CB_CallerType.Enabled = False
CB_CallerType.Value = " "
CB_CallerType.BackColor = vbScrollBars
Txt_SecLevMea.Enabled = False
Txt_SecLevMea.Value = " "
Txt_SecLevMea.BackColor = vbScrollBars
Me.MP_CallInfo.Pages("Pg_Rep").Visible = True
MP_CallNotes.Visible = False
MP_CallInfo.Pages("Pg_Rep").Visible = False

End If

End Sub

Private Sub TB_Pin_Change()
Dim AuthenticationPIN As String
'to enable the specific input fields if there is a pin on the account

If TB_Pin.Value = True Then
TB_Pin.Caption = "YES!"

AuthenticationPIN = MsgBox(" Did the Taxpayer used their PIN to authenticate themselves on HCCS ? ", vbCritical + vbYesNoCancel, "Authentication status")

If AuthenticationPIN = vbYes Then
Frm_DOB.Enabled = False
Frm_Address.Enabled = False
OB_DOBY.Enabled = False
OB_DOBN.Enabled = False
OB_AddressY.Enabled = False
OB_AddressN.Enabled = False
Txt_SecLevMea.Value = "Authentication Status PIN"
ElseIf AuthenticationPIN = vbNo Then
TB_Pin.Value = False
TB_Pin.Caption = "No!"
OB_DOBY.Enabled = True
OB_DOBN.Enabled = True
OB_AddressY.Enabled = True
OB_AddressN.Enabled = True
Txt_SecLevMea.Value = " Enhanced PIN not Used"
End If

Else
TB_Pin.Value = False
TB_Pin.Caption = "NO!"
Frm_DOB.Enabled = True
Frm_Address.Enabled = True
OB_DOBY.Enabled = True
OB_DOBN.Enabled = True
OB_AddressY.Enabled = True
OB_AddressN.Enabled = True
Txt_SecLevMea.Value = " "
Txt_SecLevMea.Value = " Enhanced PIN not Used"
End If


End Sub
Private Sub CB_CallerType_Change()

'to change the Rep text boxes to be inactive when not needed

If CB_CallerType.Value = "Taxpayer" Then
Me.MP_CallInfo.Pages("Pg_Rep").Visible = False
Txt_RepID.Enabled = False
Txt_RepID.BackColor = vbScrollBars
Txt_RepName.Enabled = False
Txt_RepName.BackColor = vbScrollBars
Txt_RepComp.Enabled = False
Txt_RepComp.BackColor = vbScrollBars
CB_RepAuth.Enabled = False
CB_RepAuth.BackColor = vbScrollBars
Frm_PIN.Enabled = True
TB_Pin.Enabled = True
ElseIf CB_CallerType.Value = "Rep" Then
Me.MP_CallInfo.Pages("Pg_Rep").Visible = True
Txt_RepID.Enabled = True
Txt_RepID.BackColor = vbWindowBackground
Txt_RepName.Enabled = True
Txt_RepName.BackColor = vbWindowBackground
Txt_RepComp.Enabled = True
Txt_RepComp.BackColor = vbWindowBackground
Txt_RepID.TabIndex = 0
CB_RepAuth.Enabled = True
CB_RepAuth.BackColor = vbWindowBackground
Frm_PIN.Enabled = False
TB_Pin.Enabled = False
End If

End Sub


Private Sub CB_ELCSNPTemp_Change()
'to lookup note pad templates,
On Error Resume Next
Me.Txt_ELCSNPTemp.Text = Application.WorksheetFunction.VLookup(Me.CB_ELCSNPTemp.Value, [ELCS], 2, False) ' to change the value in the ELCS Notepad Template text box.
On Error GoTo 0
End Sub


Private Sub OB_SINY_Click()
SANDTOMBTextbox
End Sub
Private Sub OB_SINN_Click()
SANDTOMBTextbox
End Sub
Private Sub OB_AddressY_Click()
SANDTOMBTextbox
End Sub
Private Sub OB_AddressN_Click()
SANDTOMBTextbox
End Sub
Private Sub OB_DOBY_Click()
SANDTOMBTextbox
End Sub
Private Sub OB_DOBN_Click()
SANDTOMBTextbox
End Sub
Private Sub OB_NameY_Click()
SANDTOMBTextbox
End Sub
Private Sub OB_NameN_Click()
SANDTOMBTextbox
End Sub
'And so on setting click event for each option to 'SetTextbox'

Private Sub SANDTOMBTextbox()

If OB_SINY.Value = True And _
OB_AddressY.Value = True And _
OB_DOBY.Value = True And _
OB_NameY.Value = True Then
Txt_TOMB.Value = "SAND Met"
ElseIf OB_SINN.Value = True And _
OB_AddressY.Value = True And _
OB_DOBY.Value = True And _
OB_NameY.Value = True Then _
Txt_TOMB.Value = "SAND Not Met"
ElseIf OB_AddressN.Value = True And _
OB_SINY.Value = True And _
OB_DOBY.Value = True And _
OB_NameY.Value = True Then _
Txt_TOMB.Value = "SAND Not Met"
ElseIf OB_DOBN.Value = True And _
OB_SINY.Value = True And _
OB_AddressY.Value = True And _
OB_NameY.Value = True Then _
Txt_TOMB.Value = "SAND Not Met"
ElseIf OB_NameN.Value = True And _
OB_SINY.Value = True And _
OB_AddressY.Value = True And _
OB_DOBY.Value = True Then _
Txt_TOMB.Value = "SAND Not Met"
ElseIf TB_Pin.Value = True And _
OB_SINY.Value = True And _
OB_NameY.Value = True Then
Txt_TOMB.Value = "PIN SAND Met"
ElseIf TB_Pin.Value = True And _
OB_SINY.Value = True And _
OB_NameN.Value = True Then
Txt_TOMB.Value = "PIN SAND Not Met"
End If

End Sub
Private Sub Txt_TOMB_Change()
Dim CheckELCSNP As String
Dim AnswerNo As String
Dim CallverificationFailed As String

'to enable the specific input fields if there is a pin on the account

If Txt_TOMB.Value = "SAND Met" Or _
Txt_TOMB.Value = "PIN SAND Met" Then

CheckELCSNP = MsgBox(" Does the ELCS Notepad indicates that the taxpayer failed caller verification in the past 30 days ? ", vbCritical + vbYesNo, "Step 4 Check ELCS Notepad")

If CheckELCSNP = vbNo Then

SecurityLevelMeasure_UF.Show

'AnswerNo = MsgBox("Does this Request require Enchanced Security Verification ?" & vbNewLine & "Hit Cancel if Caller used PIN to Authenticate themselves ", vbCritical + vbYesNoCancel, "Security Verification")

'If AnswerNo = vbYes Then
'Txt_SecLevMea.Value = "Enhanced"
'ElseIf AnswerNo = vbNo Then
'Txt_SecLevMea.Value = "Regular"
'End If

ElseIf CheckELCSNP = vbYes Then

CallverificationFailed = MsgBox(" Is the level of this call (regular or enhanced) the same as the previously failed call(s) ", vbExclamation + vbYesNo, "Step 4 Check ELCS Notepad")

If CallverificationFailed = vbYes Then

CallerVerificationPST_UF.Show


End If

End If

End If
End Sub

Private Sub OB_SecQue1Y_Click()
SecQueTextbox
End Sub
Private Sub OB_SecQue1N_Click()
SecQueTextbox
End Sub
Private Sub OB_SecQue2Y_Click()
SecQueTextbox
End Sub
Private Sub OB_SecQue2N_Click()
SecQueTextbox
End Sub
Private Sub OB_SecQue3Y_Click()
SecQueTextbox
End Sub
Private Sub OB_SecQue3N_Click()
SecQueTextbox
End Sub
Private Sub OB_SecQue4Y_Click()
SecQueTextbox
End Sub
Private Sub OB_SecQue4N_Click()
SecQueTextbox
End Sub
'And so on setting click event for each option to 'SetTextbox'


Private Sub SecQueTextbox()

'Two questions for the pass or fail

If OB_SecQue1Y.Value = True And _
OB_SecQue2Y.Value = True Then
Txt_SecQue1PF.Value = "Passed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2N.Value = True Then
Txt_SecQue1PF.Value = "Failed"
ElseIf OB_SecQue1Y.Value = True And _
OB_SecQue2N.Value = True Then
Txt_SecQue1PF.Value = "Failed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2Y.Value = True Then
Txt_SecQue1PF.Value = "Failed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue1N.Value = True Then
Txt_SecQue1PF.Value = "Failed"
End If


'if one of the first two questions is wrong a second page open for another two question.
If Txt_SecQue1PF.Value = "Failed" Then
MP_SecQue.Pages("Pg_SecQue2").Visible = True
ElseIf Txt_SecQue1PF.Value = "Passed" Then
MP_SecQue.Pages("Pg_SecQue2").Visible = False And _
Txt_SecQue3.Enabled = False And _
Txt_SecQue4.Enabled = False And _
OB_SecQue3Y.Enabled = False And _
OB_SecQue3N.Enabled = False And _
OB_SecQue4Y.Enabled = False And _
OB_SecQue4N.Enabled = False
End If


'Three questions for the pass or fail

If OB_SecQue1Y.Value = True And _
OB_SecQue2N.Value = True And _
OB_SecQue3Y.Value = True Then
Txt_SecQue1PF.Value = "Passed"
Txt_SecQue2PF.Value = "Passed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2Y.Value = True And _
OB_SecQue3Y.Value = True Then
Txt_SecQue1PF.Value = "Passed"
Txt_SecQue2PF.Value = "Passed"
ElseIf OB_SecQue1Y.Value = True And _
OB_SecQue2N.Value = True And _
OB_SecQue3N.Value = True Then
Txt_SecQue1PF.Value = "Failed"
Txt_SecQue2PF.Value = "Failed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2Y.Value = True And _
OB_SecQue3N.Value = True Then
Txt_SecQue1PF.Value = "Failed"
Txt_SecQue2PF.Value = " "
ElseIf OB_SecQue1Y.Value = True And _
OB_SecQue2N.Value = True And _
OB_SecQue3N.Value = True Then
Txt_SecQue1PF.Value = "Failed"
Txt_SecQue2PF.Value = " "
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2N.Value = True And _
OB_SecQue3N.Value = True Then
Txt_SecQue1PF.Value = "Failed"
Txt_SecQue2PF.Value = "Failed"
End If

'Four questions for the pass or fail

If OB_SecQue1Y.Value = True And _
OB_SecQue2N.Value = True And _
OB_SecQue3N.Value = True And _
OB_SecQue4Y.Value = True Then
Txt_SecQue1PF.Value = "Passed"
Txt_SecQue2PF.Value = "Passed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2Y.Value = True And _
OB_SecQue3N.Value = True And _
OB_SecQue4Y.Value = True Then
Txt_SecQue1PF.Value = "Passed"
Txt_SecQue2PF.Value = "Passed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2N.Value = True And _
OB_SecQue3Y.Value = True And _
OB_SecQue4Y.Value = True Then
Txt_SecQue1PF.Value = "Passed"
Txt_SecQue2PF.Value = "Passed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2N.Value = True And _
OB_SecQue3N.Value = True And _
OB_SecQue4N.Value = True Then
Txt_SecQue1PF.Value = "Failed"
Txt_SecQue2PF.Value = "Failed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2Y.Value = True And _
OB_SecQue3N.Value = True And _
OB_SecQue4N.Value = True Then
Txt_SecQue1PF.Value = "Failed"
Txt_SecQue2PF.Value = "Failed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2N.Value = True And _
OB_SecQue3Y.Value = True And _
OB_SecQue4N.Value = True Then
Txt_SecQue1PF.Value = "Failed"
Txt_SecQue2PF.Value = "Failed"
ElseIf OB_SecQue1N.Value = True And _
OB_SecQue2N.Value = True And _
OB_SecQue3N.Value = True And _
OB_SecQue4Y.Value = True Then
Txt_SecQue1PF.Value = "Failed"
Txt_SecQue2PF.Value = "Failed"
End If

End Sub


Private Sub CMDB_WriteNote_Click()

'to combine all the feild for input into TSAD-i

'"," & " Security Questions: " & "< " & Txt_SecQue.Value & " >" &

Txt_TSADiNP.Value = Txt_POC.Value & _
"," & vbNewLine & " TOMB/SNDA: " & "< " & Txt_TOMB.Value & " >" & "," & " Answered " & "< "" 1 " & Txt_SecQue1.Value & " " & OB_SecQue1Y.Value & " > <"",2 " & Txt_SecQue2.Value & " " & OB_SecQue2Y.Value & " >" & _
"," & " Answered " & "< "",3 " & Txt_SecQue3.Value & " " & OB_SecQue3Y.Value & ">< "",4 " & Txt_SecQue4.Value & " " & OB_SecQue4Y.Value & " >" & _
"," & " Security Level Measure: " & "< " & Txt_SecLevMea.Value & " " & Txt_SecQue1PF.Value & " >" & _
"," & vbNewLine & " Call Notes: " & "< " & Txt_CallNotes.Value & " >" & _
"," & vbNewLine & " Caller Type: " & "< " & CB_CallerType.Value & " >" & _
"," & " Call Teir: " & "< " & CB_CallTier.Value & " >" & _
"," & vbNewLine & " Rep ID: " & "< " & Txt_RepID.Value & " >" & _
"," & " Rep Name: " & "< " & Txt_RepName.Value & " >" & _
"," & " Rep Company: " & "< " & Txt_RepComp.Value & " >" & _
"," & " Rep Authorization: " & "< " & CB_RepAuth.Value & " >"

End Sub
 
Upvote 0
Sorry, I'm not even going to try to read all that. If any of it is not applicable it shouldn't be there in my humble opinion. The main issue is that you didn't post it within vba code tags (see posting toolbar) so there's no formatting, no indentation. It's just a mass of characters that are too hard to make sense of.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Select ALL fields on UserForm and make them read only in VBA
Replies
2
Views
122
adambc
A
W
  • Question
Posting Data with new form text boxes
Replies
0
Views
255
woodland81
W
N
  • Question
Automating textbox updates in PP using VBA to read excel data
Replies
0
Views
447
noobslayer252
N
P
  • Solved
Advice needed on where to put code that stops duplicate entries
Replies
5
Views
309
Pacman52
P
P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
436
pure vito
P

Forum statistics

Threads
1,214,970
Messages
6,122,514
Members
449,088
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top