I have a string variable called sTRID which I would like to put in a text
field in a userform. Like "The client sTRID is not in the database. Do you
want to add them?"
I want the actual client ID to appear in place of the string variable. Is
this possible?
Thanks!

msg = "The client's " & TRID & " is not in the datbase." _
& vbCrLf Do you want to add them?"
MsgBox msg

"salgud" wrote:

I have a string variable called sTRID which I would like to put in a text
field in a userform. Like "The client sTRID is not in the database. Do you
want to add them?"
I want the actual client ID to appear in place of the string variable. Is
this possible?
Thanks!

On Wed, 30 Jul 2008 12:41:17 -0700, JLGWhiz wrote:

msg = "The client's " & TRID & " is not in the datbase." _
& vbCrLf Do you want to add them?"
MsgBox msg

"salgud" wrote:

I have a string variable called sTRID which I would like to put in a text
field in a userform. Like "The client sTRID is not in the database. Do you
want to add them?"
I want the actual client ID to appear in place of the string variable. Is
this possible?
Thanks!


Thanks for the reply. I know how to do this for a MsgBox. I'm trying to
figure out how to do it in a userform, as I stated in my original post.

This is only an example, but it will do what you want if you have a textbox
and a label on a userform with the textbox placed before the label.

Private Sub UserForm_Initialize()
With UserForm1
.Label1.Caption = "Doesn't Exist would you like to create it?"
.TextBox1.Value = sTRID
End With
End Sub


"salgud" wrote:

On Wed, 30 Jul 2008 12:41:17 -0700, JLGWhiz wrote:

msg = "The client's " & TRID & " is not in the datbase." _
& vbCrLf Do you want to add them?"
MsgBox msg

"salgud" wrote:

I have a string variable called sTRID which I would like to put in a text
field in a userform. Like "The client sTRID is not in the database. Do you
want to add them?"
I want the actual client ID to appear in place of the string variable. Is
this possible?
Thanks!


Thanks for the reply. I know how to do this for a MsgBox. I'm trying to
figure out how to do it in a userform, as I stated in my original post.

The example was to illustrate how to get the string variable stored with a
message into another variable. You can then take that variable and use it
anywhere in the code to reproduce the message.

Sub UserForm1_Initialize()
Dim msg As String
msg = "The client's " & TRID & " is not in the datbase." _
& vbCrLf Do you want to add them?"
TextBox1.Text = msg
Label1.Text = msg
UserForm1.Caption = msg
End Sub

As you see, it can be put in several places of your choice once stored in
the variable. The variable "msg" just makes it easier to use without having
to retype the entire phrase each time. Maybe I don't understand what you are
after.

"salgud" wrote:

On Wed, 30 Jul 2008 12:41:17 -0700, JLGWhiz wrote:

msg = "The client's " & TRID & " is not in the datbase." _
& vbCrLf Do you want to add them?"
MsgBox msg

"salgud" wrote:

I have a string variable called sTRID which I would like to put in a text
field in a userform. Like "The client sTRID is not in the database. Do you
want to add them?"
I want the actual client ID to appear in place of the string variable. Is
this possible?
Thanks!


Thanks for the reply. I know how to do this for a MsgBox. I'm trying to
figure out how to do it in a userform, as I stated in my original post.

On Thu, 31 Jul 2008 08:21:00 -0700, JLGWhiz wrote:

The example was to illustrate how to get the string variable stored with a
message into another variable. You can then take that variable and use it
anywhere in the code to reproduce the message.

Sub UserForm1_Initialize()
Dim msg As String
msg = "The client's " & TRID & " is not in the datbase." _
& vbCrLf Do you want to add them?"
TextBox1.Text = msg
Label1.Text = msg
UserForm1.Caption = msg
End Sub

As you see, it can be put in several places of your choice once stored in
the variable. The variable "msg" just makes it easier to use without having
to retype the entire phrase each time. Maybe I don't understand what you are
after.

Ok, I see what you're saying now. Thanks!

On Thu, 31 Jul 2008 08:21:00 -0700, JLGWhiz wrote:

The example was to illustrate how to get the string variable stored with a
message into another variable. You can then take that variable and use it
anywhere in the code to reproduce the message.

Sub UserForm1_Initialize()
Dim msg As String
msg = "The client's " & TRID & " is not in the datbase." _
& vbCrLf Do you want to add them?"
TextBox1.Text = msg
Label1.Text = msg
UserForm1.Caption = msg
End Sub

As you see, it can be put in several places of your choice once stored in
the variable. The variable "msg" just makes it easier to use without having
to retype the entire phrase each time. Maybe I don't understand what you are
after.

"salgud" wrote:

On Wed, 30 Jul 2008 12:41:17 -0700, JLGWhiz wrote:

msg = "The client's " & TRID & " is not in the datbase." _
& vbCrLf Do you want to add them?"
MsgBox msg

"salgud" wrote:

I have a string variable called sTRID which I would like to put in a text
field in a userform. Like "The client sTRID is not in the database. Do you
want to add them?"
I want the actual client ID to appear in place of the string variable. Is
this possible?
Thanks!


Thanks for the reply. I know how to do this for a MsgBox. I'm trying to
figure out how to do it in a userform, as I stated in my original post.


Trying to implement this, but it's different than what I've done with
userforms before. What does userform "initialize" do? I've always used
"userform.show" to display a userform. Then put the necessary code in the
userform. I added your code, but it doesn't recognize "Label1, even though
it's there in the userform.

Option Explicit
Public rTRDates As Range
Public rHistStart As Range
Public sTRID As String

Public Sub TribalInvCheck()
Dim wbTribalHist As Workbook
Dim wbTribalTR As Workbook
Dim wsTribalTR As Worksheet
Dim wsTribalHist As Worksheet
Dim rTRCell As Range
Dim lTRRow As Long
Dim lHistRow As Long
Dim rFoundID As Range

Dim rTribalHist As Range
Dim lHistCol As Long
Dim rHistFin As Range
Dim rTotals As Range
Dim dHistStart As Date
Dim dHistFin As Date
Dim dTRStart As Date
Dim dTRFin As Date
Dim bConflict As Boolean
Dim rCell As Range

Set wbTribalHist = ThisWorkbook
Set wbTribalTR = ActiveWorkbook
Set wsTribalTR = ActiveSheet
Set wsTribalHist = wbTribalHist.Worksheets("Historical")
Set rTribalHist = wsTribalHist.Range("A3:IV3")

bConflict = False

'Application.ScreenUpdating = False

If ThisWorkbook.Name = ActiveWorkbook.Name Then
MsgBox "Please do not run this macro from the workbook that contains it."
_
& Chr(10) & "Please select a Turnaround Report and then restart this
macro."
Exit Sub
End If

wsTribalTR.Cells.Font.Colorindex = 0
wsTribalHist.Cells.Font.Color = 0
wsTribalTR.Columns("O").Clear
wsTribalHist.Range("C5:C120").Clear
wsTribalHist.Range("F5:F120").Clear
wsTribalHist.Range("I5:I120").Clear

'rTRCell.Select

'### Add code to add new client to Tribal Invoice Check if client not found
'### Add code to handle client ID blank (add to Tribal Template macro too)

'wsTribalHist.Activate
'rFoundID.Select

lTRRow = 3
Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
Set rTotals = rTRCell.Offset(0, 7)

wsTribalHist.Activate

'Do loop until totals column shows "Monthly Totals"
Do While rTotals.Value < "Monthly Totals"

'test for Totals row, skip
Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
Set rTotals = rTRCell.Offset(0, 7)

If rTotals.Value < "Totals" Then
sTRID = rTRCell.Value

'Test for blank Client ID field
If sTRID = "" Then

'# Add more to this error routine/message
MsgBox "Blank Client"
Exit Sub
End If

Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues)

'Test if Client ID not found in Historical sheet and offer to add new
client
On Error Resume Next
If rFoundID = "" Then
MsgBox "The current ClientID from the TR does not exist in the
Historical worksheet." _
& "Should this ClientID be added?"
Call UserForm1_Initialize
'### Add a form and show it from here. Options include "Add new
client",
'"Skip this entry", then return to here
Exit Sub
End If

lHistRow = rFoundID.Row + 2
lHistCol = rFoundID.Column
Set rHistStart = wsTribalHist.Cells(lHistRow, lHistCol)
Set rTRDates = Range(rTotals.Offset(0, -1), rTotals)
Set rHistFin = rHistStart.Offset(0, 1)
dHistStart = rHistStart.Value
dHistFin = rHistFin.Value
dTRStart = rTotals.Offset(0, -1).Value
dTRFin = rTotals.Value

If dHistFin < 0 Then
'If there is a HistFin date then
If dTRStart <= dHistFin Then
Do While dHistStart < ""
If dTRFin <= dHistStart Then

Else
If dTRStart <= dHistFin Then

'CONFLICT
'Mark row in Historical data sheet where conflict occurred
With rHistFin.Offset(0, 1)
.Value = "C " & dTRStart
.Font.Colorindex = 3
End With

'Mark row in Tribal sheet where conflict occurred
wsTribalTR.Cells(lTRRow, "O") = "C"
Set rCell = wsTribalTR.Cells(lTRRow, "O")
rCell.Font.Colorindex = 3
bConflict = True
GoTo BigLoop
End If
End If
lHistRow = lHistRow + 1
Set rHistStart = wsTribalHist.Cells(lHistRow, lHistCol)
dHistStart = rHistStart.Value
Loop
End If

End If

If bConflict = False Then
'Mark rows entered in Historical sheet with E
wsTribalTR.Cells(lTRRow, "O") = "E"
Call InsertNewData
End If
lHistRow = lHistRow + 1
End If
BigLoop:
lTRRow = lTRRow + 1
Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
Set rTotals = rTRCell.Offset(0, 7)

Loop
If bConflict = True Then MsgBox "Conflicts were found and marked on both
worksheets."
Application.ScreenUpdating = True

End Sub


Sub UserForm1_Initialize()
Dim sMsg As String
sMsg = "The client's " & sTRID & " is not in the datbase." _
& vbCrLf & "Do you want to add them?"
' TextBox1.Text = msg
Label1.Text = sMsg <----- COMPILE ERROR, VARIABLE NOT DEFINED
' UserForm1.Caption = msg
End Sub

Does "Label1" have to be declared somethow? If so, what kind of variable is
it? It is there in the userform.