Textbox in userform doing weird things
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! |
Textbox in userform doing weird things
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! |
Textbox in userform doing weird things
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! |
All times are GMT +1. The time now is 04:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com