Hi,
I enter address details in activex worksheet multiline textbox1.
CmdUpdate transfers textbox1.value to Sheets("Data") to a merged range B4:H8.
When Sheets("Data") prints to pdf, a "?" within a square prints at each carriage return.
How can i clean this?
I tried replace "?" with "" but does not work.
Please advise.
Many Thanks
Mohamed
Cleaning data in Multiline Worksheet TextBox
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cleaning data in Multiline Worksheet TextBox
What is the code behind cmdUpdate?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Cleaning data in Multiline Worksheet TextBox
In the worksheet module:
Private Sub cmdUpdate_Click()
Dim rPractitioner As Range
Dim rGreeting As Range
Dim rOwedByTo As Range
Dim rParty1 As Range
Dim rPreposition As Range
Dim rParty2 As Range
Dim rngDate As Range
Dim rLoanAmt As Range
Dim rLoanSecured As Range
Dim rLoanIntFree As Range
Dim rSignatory As Range
Set rPractitioner = Sheets("Data").Range("B4")
Set rGreeting = Sheets("Data").Range("B11")
Set rOwedByTo = Sheets("Data").Range("AA2")
Set rParty1 = Sheets("Data").Range("AC2")
Set rPreposition = Sheets("Data").Range("AE2")
Set rParty2 = Sheets("Data").Range("AG2")
Set rngDate = Sheets("Data").Range("AI2")
Set rLoanAmt = Sheets("Data").Range("AK2")
Set rLoanSecured = Sheets("Data").Range("B25")
Set rLoanIntFree = Sheets("Data").Range("B27")
Set rSignatory = Sheets("Data").Range("B41")
With Application
.EnableCancelKey = xlDisabled
.EnableEvents = False
.ScreenUpdating = False
End With
With Me
If (.txtPractitioner.Value <> "" And _
.txtGreeting.Value <> "" And _
.LxBxOwedBy_To.ListIndex > -1 And _
.txtParty1.Value <> "" And _
.txtPreposition.Value <> "" And _
.txtParty2.Value <> "" And _
.DTPicker1.Value <> "" And _
.txtLoanAmt.Value <> "" And _
.LxBxLoanSecured.ListIndex > -1 And _
.LxBxInterestFree.ListIndex > -1 And _
.txbxSignatureLine.Value <> "") Then
With Sheets("Data")
rPractitioner.Value = Me.txtPractitioner.Value
rGreeting.Value = Me.txtGreeting.Value
rOwedByTo.Value = Me.LxBxOwedBy_To.Value
rParty1.Value = Me.txtParty1.Value
rPreposition.Value = Me.txtPreposition.Value
rParty2.Value = Me.txtParty2.Value
rngDate.Value = Me.DTPicker1.Value
rLoanAmt.Value = Me.txtLoanAmt.Value
rLoanSecured.Value = Me.LxBxLoanSecured.Value
rSignatory.Value = Me.txbxSignatureLine.Value
If Me.LxBxInterestFree.ListIndex = 1 Then
rLoanIntFree.Value = Me.LxBxInterestFree.Value & " " & Me.txtIntRate.Value
Else
rLoanIntFree.Value = Me.LxBxInterestFree.Value
End If
End With
Else
Msgbox "Please complete all fields!", vbExclamation, "Incomplete Data"
End If
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Private Sub cmdUpdate_Click()
Dim rPractitioner As Range
Dim rGreeting As Range
Dim rOwedByTo As Range
Dim rParty1 As Range
Dim rPreposition As Range
Dim rParty2 As Range
Dim rngDate As Range
Dim rLoanAmt As Range
Dim rLoanSecured As Range
Dim rLoanIntFree As Range
Dim rSignatory As Range
Set rPractitioner = Sheets("Data").Range("B4")
Set rGreeting = Sheets("Data").Range("B11")
Set rOwedByTo = Sheets("Data").Range("AA2")
Set rParty1 = Sheets("Data").Range("AC2")
Set rPreposition = Sheets("Data").Range("AE2")
Set rParty2 = Sheets("Data").Range("AG2")
Set rngDate = Sheets("Data").Range("AI2")
Set rLoanAmt = Sheets("Data").Range("AK2")
Set rLoanSecured = Sheets("Data").Range("B25")
Set rLoanIntFree = Sheets("Data").Range("B27")
Set rSignatory = Sheets("Data").Range("B41")
With Application
.EnableCancelKey = xlDisabled
.EnableEvents = False
.ScreenUpdating = False
End With
With Me
If (.txtPractitioner.Value <> "" And _
.txtGreeting.Value <> "" And _
.LxBxOwedBy_To.ListIndex > -1 And _
.txtParty1.Value <> "" And _
.txtPreposition.Value <> "" And _
.txtParty2.Value <> "" And _
.DTPicker1.Value <> "" And _
.txtLoanAmt.Value <> "" And _
.LxBxLoanSecured.ListIndex > -1 And _
.LxBxInterestFree.ListIndex > -1 And _
.txbxSignatureLine.Value <> "") Then
With Sheets("Data")
rPractitioner.Value = Me.txtPractitioner.Value
rGreeting.Value = Me.txtGreeting.Value
rOwedByTo.Value = Me.LxBxOwedBy_To.Value
rParty1.Value = Me.txtParty1.Value
rPreposition.Value = Me.txtPreposition.Value
rParty2.Value = Me.txtParty2.Value
rngDate.Value = Me.DTPicker1.Value
rLoanAmt.Value = Me.txtLoanAmt.Value
rLoanSecured.Value = Me.LxBxLoanSecured.Value
rSignatory.Value = Me.txbxSignatureLine.Value
If Me.LxBxInterestFree.ListIndex = 1 Then
rLoanIntFree.Value = Me.LxBxInterestFree.Value & " " & Me.txtIntRate.Value
Else
rLoanIntFree.Value = Me.LxBxInterestFree.Value
End If
End With
Else
Msgbox "Please complete all fields!", vbExclamation, "Incomplete Data"
End If
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Cleaning data in Multiline Worksheet TextBox
Sorry, i should have added to the last post:
txtPractitioner is the multiline textbox wherein the addressees details are entered.
Kind Regards
Mohamed
txtPractitioner is the multiline textbox wherein the addressees details are entered.
Kind Regards
Mohamed
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cleaning data in Multiline Worksheet TextBox
Change
rPractitioner.Value = Me.txtPractitioner.Value
to
rPractitioner.Value = Replace(Me.txtPractitioner.Value, vbCrLF, vbLf)
Remark 1: you don't need With Sheets("Data") and the corresponding End With. You don't do anything with it.
Remark 2: you can omit the keyword Me within the With Me ... End With block.
rPractitioner.Value = Me.txtPractitioner.Value
to
rPractitioner.Value = Replace(Me.txtPractitioner.Value, vbCrLF, vbLf)
Remark 1: you don't need With Sheets("Data") and the corresponding End With. You don't do anything with it.
Remark 2: you can omit the keyword Me within the With Me ... End With block.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Cleaning data in Multiline Worksheet TextBox
Thanks Hans,
For the solution & for the remarks.
Solution worked perfectly.
Kind Regards
Mohamed
For the solution & for the remarks.
Solution worked perfectly.
Kind Regards
Mohamed