I have 2 textboxes in a userform that I use to create a spreadsheet.
For some reason, I have to define the variables they get twice, once in
the textbox code, and once in the macro. I haven't had to do that
before, and can't figure out why it's this way now. If I remark out
either set of definitions, the macro hangs up one way or another. The
code for the textboxes is:

Private Sub tbFacilName_Change()
sFacilName = tbFacilName.Text

End Sub

Private Sub tbFacilRows_Change()
lFacilRows = tbFacilRows.Value

End Sub

The macro is:

Sub CreateTribalSheetR1()
Dim sFacilName As String
Dim lNextRow As Long
Dim lBaseRow As Long
Dim lLimitRow As Long
Dim lFacilName As Long
Dim lPrevSumRow As Long
Dim ws As Worksheet

lNextRow = 2
lBaseRow = lNextRow
lPrevSumRow = 1

Set ws = ActiveSheet

Sheets("Source").Select
' Enter column headers from Source sheet
Range("A1:N1").Select
Selection.Copy

' How to select current sheet?
ws.Select
Range("a1").Select
ActiveSheet.Paste

frmFacil.Show

If vbCancel = True Then Exit Sub
sFacilName = frmFacil.tbFacilName.Text

lFacilRows = frmFacil.tbFacilRows.Value

If sFacilName < "" Then
lLimitRow = lBaseRow + lFacilRows
lPrevSumRow = lNextRow
Do Until lNextRow = lLimitRow
With ActiveSheet
.Cells(lNextRow, 2) = sFacilName

' insert formula
=IF(G2<"",DATEDIF(G2,H2,"d")+1,"") with lPrevSumRow as the row
.Cells(lNextRow, "I").Formula = "=IF(G" & lNextRow &
"<"""",DATEDIF(G" _
& lNextRow & ",H" & lNextRow & ",""d"")+1,"""")"

lNextRow = lNextRow + 1
End With
Loop

' Enter Totals row
With ActiveSheet
.Cells(lNextRow, "H") = "Totals"
.Cells(lNextRow, "I").Formula = "=Sum(i" & lPrevSumRow &
":i" _
& lNextRow - 1 & ")"
.Range("I" & lNextRow).Select
Selection.AutoFill Destination:=Range("I" & lNextRow & ":M"
_
& lNextRow), Type:=xlFillDefault

'Enter row totals
' =SUM(J4:M4)
.Cells(lPrevSumRow, "n").Formula = "=sum(j" & lPrevSumRow &
_
":m" & lPrevSumRow & ")"

.Range("n" & lPrevSumRow).Select
Selection.AutoFill Destination:=Range("n" & lPrevSumRow &
":n" _
& lNextRow), Type:=xlFillDefault

' Color totals row yellow
.Range("A" & lNextRow & ":" & "N" & lNextRow).Select
Selection.Interior.ColorIndex = 6

End With


Else
lFacilName = MsgBox("Do you have more facility names?", vbYesNo)
If lFacilName = vbYes Then
GoTo EnterFacilNames
Else

' Add monthly totals to bottom of sheet
' =SUMIF($H$2:$H$8,"totals",I2:I8)
With ActiveSheet
.Cells(lNextRow, "H") = "Monthly Totals"
.Cells(lNextRow, "I").Formula = "=SUMIF($h$2:$h$" & _
lNextRow - 1 & ",""totals"",i2:i" & lNextRow - 1 &
")"
Range("I" & lNextRow).Select
Selection.AutoFill Destination:=Range("I" & lNextRow &
":n" _
& lNextRow), Type:=xlFillDefault
End With

Exit Sub

End If
End If

lNextRow = lNextRow + 1
lBaseRow = lNextRow

GoTo EnterFacilNames

End Sub

I'm not sure there's anything wrong with it, just seems strange having
to define sFacilName and lFacilRows twice. Any ideas on this?

Thanks!

I should have mentioned in the above post that I also have the
following in the userform code:

Private Sub cbOK_Click()
If sFacilName < "" Then
lFacilRows = tbFacilRows.Text
Else
MsgBox "Please enter a facility name or click Cancel", vbOKOnly

End If
frmFacil.Hide
End Sub


davegb wrote:
I have 2 textboxes in a userform that I use to create a spreadsheet.
For some reason, I have to define the variables they get twice, once in
the textbox code, and once in the macro. I haven't had to do that
before, and can't figure out why it's this way now. If I remark out
either set of definitions, the macro hangs up one way or another. The
code for the textboxes is:

Private Sub tbFacilName_Change()
sFacilName = tbFacilName.Text

End Sub

Private Sub tbFacilRows_Change()
lFacilRows = tbFacilRows.Value

End Sub

The macro is:

Sub CreateTribalSheetR1()
Dim sFacilName As String
Dim lNextRow As Long
Dim lBaseRow As Long
Dim lLimitRow As Long
Dim lFacilName As Long
Dim lPrevSumRow As Long
Dim ws As Worksheet

lNextRow = 2
lBaseRow = lNextRow
lPrevSumRow = 1

Set ws = ActiveSheet

Sheets("Source").Select
' Enter column headers from Source sheet
Range("A1:N1").Select
Selection.Copy

' How to select current sheet?
ws.Select
Range("a1").Select
ActiveSheet.Paste

frmFacil.Show

If vbCancel = True Then Exit Sub
sFacilName = frmFacil.tbFacilName.Text

lFacilRows = frmFacil.tbFacilRows.Value

If sFacilName < "" Then
lLimitRow = lBaseRow + lFacilRows
lPrevSumRow = lNextRow
Do Until lNextRow = lLimitRow
With ActiveSheet
.Cells(lNextRow, 2) = sFacilName

' insert formula
=IF(G2<"",DATEDIF(G2,H2,"d")+1,"") with lPrevSumRow as the row
.Cells(lNextRow, "I").Formula = "=IF(G" & lNextRow &
"<"""",DATEDIF(G" _
& lNextRow & ",H" & lNextRow & ",""d"")+1,"""")"

lNextRow = lNextRow + 1
End With
Loop

' Enter Totals row
With ActiveSheet
.Cells(lNextRow, "H") = "Totals"
.Cells(lNextRow, "I").Formula = "=Sum(i" & lPrevSumRow &
":i" _
& lNextRow - 1 & ")"
.Range("I" & lNextRow).Select
Selection.AutoFill Destination:=Range("I" & lNextRow & ":M"
_
& lNextRow), Type:=xlFillDefault

'Enter row totals
' =SUM(J4:M4)
.Cells(lPrevSumRow, "n").Formula = "=sum(j" & lPrevSumRow &
_
":m" & lPrevSumRow & ")"

.Range("n" & lPrevSumRow).Select
Selection.AutoFill Destination:=Range("n" & lPrevSumRow &
":n" _
& lNextRow), Type:=xlFillDefault

' Color totals row yellow
.Range("A" & lNextRow & ":" & "N" & lNextRow).Select
Selection.Interior.ColorIndex = 6

End With


Else
lFacilName = MsgBox("Do you have more facility names?", vbYesNo)
If lFacilName = vbYes Then
GoTo EnterFacilNames
Else

' Add monthly totals to bottom of sheet
' =SUMIF($H$2:$H$8,"totals",I2:I8)
With ActiveSheet
.Cells(lNextRow, "H") = "Monthly Totals"
.Cells(lNextRow, "I").Formula = "=SUMIF($h$2:$h$" & _
lNextRow - 1 & ",""totals"",i2:i" & lNextRow - 1 &
")"
Range("I" & lNextRow).Select
Selection.AutoFill Destination:=Range("I" & lNextRow &
":n" _
& lNextRow), Type:=xlFillDefault
End With

Exit Sub

End If
End If

lNextRow = lNextRow + 1
lBaseRow = lNextRow

GoTo EnterFacilNames

End Sub

I'm not sure there's anything wrong with it, just seems strange having
to define sFacilName and lFacilRows twice. Any ideas on this?

Thanks!

You can make them visible my declaring them Public at the top (General)
section of your module, and take them out of the Sub. Just make sure when
using public variables that the name does not conflict with the same name
elsewhere in a sub or function. (If the same name IS declared in a sub or
function the local one takes precedence.)

Example:

Public sFacilName As String

"davegb" wrote:

I have 2 textboxes in a userform that I use to create a spreadsheet.
For some reason, I have to define the variables they get twice, once in
the textbox code, and once in the macro. I haven't had to do that
before, and can't figure out why it's this way now. If I remark out
either set of definitions, the macro hangs up one way or another. The
code for the textboxes is:

Private Sub tbFacilName_Change()
sFacilName = tbFacilName.Text

End Sub

Private Sub tbFacilRows_Change()
lFacilRows = tbFacilRows.Value

End Sub

The macro is:

Sub CreateTribalSheetR1()
Dim sFacilName As String
Dim lNextRow As Long
Dim lBaseRow As Long
Dim lLimitRow As Long
Dim lFacilName As Long
Dim lPrevSumRow As Long
Dim ws As Worksheet

lNextRow = 2
lBaseRow = lNextRow
lPrevSumRow = 1

Set ws = ActiveSheet

Sheets("Source").Select
' Enter column headers from Source sheet
Range("A1:N1").Select
Selection.Copy

' How to select current sheet?
ws.Select
Range("a1").Select
ActiveSheet.Paste

frmFacil.Show

If vbCancel = True Then Exit Sub
sFacilName = frmFacil.tbFacilName.Text

lFacilRows = frmFacil.tbFacilRows.Value

If sFacilName < "" Then
lLimitRow = lBaseRow + lFacilRows
lPrevSumRow = lNextRow
Do Until lNextRow = lLimitRow
With ActiveSheet
.Cells(lNextRow, 2) = sFacilName

' insert formula
=IF(G2<"",DATEDIF(G2,H2,"d")+1,"") with lPrevSumRow as the row
.Cells(lNextRow, "I").Formula = "=IF(G" & lNextRow &
"<"""",DATEDIF(G" _
& lNextRow & ",H" & lNextRow & ",""d"")+1,"""")"

lNextRow = lNextRow + 1
End With
Loop

' Enter Totals row
With ActiveSheet
.Cells(lNextRow, "H") = "Totals"
.Cells(lNextRow, "I").Formula = "=Sum(i" & lPrevSumRow &
":i" _
& lNextRow - 1 & ")"
.Range("I" & lNextRow).Select
Selection.AutoFill Destination:=Range("I" & lNextRow & ":M"
_
& lNextRow), Type:=xlFillDefault

'Enter row totals
' =SUM(J4:M4)
.Cells(lPrevSumRow, "n").Formula = "=sum(j" & lPrevSumRow &
_
":m" & lPrevSumRow & ")"

.Range("n" & lPrevSumRow).Select
Selection.AutoFill Destination:=Range("n" & lPrevSumRow &
":n" _
& lNextRow), Type:=xlFillDefault

' Color totals row yellow
.Range("A" & lNextRow & ":" & "N" & lNextRow).Select
Selection.Interior.ColorIndex = 6

End With


Else
lFacilName = MsgBox("Do you have more facility names?", vbYesNo)
If lFacilName = vbYes Then
GoTo EnterFacilNames
Else

' Add monthly totals to bottom of sheet
' =SUMIF($H$2:$H$8,"totals",I2:I8)
With ActiveSheet
.Cells(lNextRow, "H") = "Monthly Totals"
.Cells(lNextRow, "I").Formula = "=SUMIF($h$2:$h$" & _
lNextRow - 1 & ",""totals"",i2:i" & lNextRow - 1 &
")"
Range("I" & lNextRow).Select
Selection.AutoFill Destination:=Range("I" & lNextRow &
":n" _
& lNextRow), Type:=xlFillDefault
End With

Exit Sub

End If
End If

lNextRow = lNextRow + 1
lBaseRow = lNextRow

GoTo EnterFacilNames

End Sub

I'm not sure there's anything wrong with it, just seems strange having
to define sFacilName and lFacilRows twice. Any ideas on this?

Thanks!