Form Fun...

T

tonyDeBrasco

Hi,

How do you assign whatever is entered in a textbox in a form to
variable?

For example I want to be able to select up to 6 different worksheet
and within these worksheets then go and single out the spreadshee
titled Totals. Then I must create another spread sheet which wil
solely contain the data that is entered on all the individual totals.

This is similar to my other post except the added complexity of form
is now in play. I have already set the form up just need help with th
hard part.

Many thanks,
Ton
 
B

Bob Phillips

myVar = Textbox1.Text

but I don't think that is what you mean (although it is what I think you are
asking :))
 
T

tonyDeBrasco

thanks for that... I assume I must already have myVar defined a
something..

So the overall thing I want to achieve is being able to copy data fro
lots of spreadsheets and each of these sheets is in an unopene
workbook. Then all this data will be combined to form an overal
totals.

if my thinking is correct hopefully something like this would at leas
start what im trying to do, if anybody can take my fragmented code an
run with it a bit i'd be most grateful. As I have the form set up th
only function button that I have to get working is the "Generat
Totals" (CommandButton1_Click()) button...

Also as I am new to Excel and VB I will attempt to provide comment
showing what I think all of the lines mean, if I am wrong pleas
correct

Private Sub CommandButton1_Click()

Dim sh As Worksheet //new variable of type worksheet
Dim myVar As String //variable of type string
Dim DestSh As Worksheet
Dim shLast As Long //variable of type long
Dim Last As Long
myVar = TextBox3.Text //name of file entered by user - should it b
the entire path eg C:// etc...???
sh = myVar //assigning the text entered by user as the name of th
worksheet


On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Overall Totals").Name) = 0 The
//check to see if sheet exists

Application.ScreenUpdating = False //don't know
Set DestSh = ThisWorkbook.Worksheets.Add //adding a sheet to th
current workbook
DestSh.Name = "Overall Totals" //naming that new sheet
For Each sh In ThisWorkbook.Worksheets //I think ThisWorkboo
should be myVar??
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh) //last row = function to fin
last row
shLast = LastRow(sh) //so we know how many rows t
copy
sh.Range(sh.Rows(5), sh.Rows(shLast)).Cop
DestSh.Cells(Last + 1, "A") //copying the desired
End If
Next
DestSh.Cells(1).Select //don't know
Application.ScreenUpdating = True //don't know
Else
MsgBox "Delete the current overall totals and then repopulate"
End If

End Sub

I can't take credit for the code, it is compliments of Ron DeBruin ba
the bit at the top that doesn't work..

Hopefully someone can help..
 
B

Bob Phillips

Here is a tidied up version of your code, but I cannot see where other
workbooks or textboxes come into it, other than the initial textbox.

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim myVar As String
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

myVar = TextBox3.Text 'name of file entered by user
'YES, itshould it be the entire path
On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Overall Totals").Name) = 0 Then

Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Overall Totals"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)
sh.Range(sh.Rows(5), sh.Rows(shLast)).Copy _
DestSh.Cells(Last + 1, "A")
End If
Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "Delete the current overall totals and then repopulate"
End If

End Sub
 
Top