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.
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.
