Hi,

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

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

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

Many thanks,
Tony


--
tonyDeBrascoPosted from http://www.pcreview.co.uk/ newsgroup access

myVar = Textbox1.Text

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

--
HTH

Bob Phillips

"tonyDeBrasco" <tonyDeBrasco.1skhfy@ wrote in message
...

Hi,

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

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

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

Many thanks,
Tony


--
tonyDeBrascoPosted from http://www.pcreview.co.uk/ newsgroup access

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

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

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

Also as I am new to Excel and VB I will attempt to provide comments
showing what I think all of the lines mean, if I am wrong please
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 be
the entire path eg C:// etc...???
sh = myVar //assigning the text entered by user as the name of the
worksheet


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

Application.ScreenUpdating = False //don't know
Set DestSh = ThisWorkbook.Worksheets.Add //adding a sheet to the
current workbook
DestSh.Name = "Overall Totals" //naming that new sheet
For Each sh In ThisWorkbook.Worksheets //I think ThisWorkbook
should be myVar??
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh) //last row = function to find
last row
shLast = LastRow(sh) //so we know how many rows to
copy
sh.Range(sh.Rows(5), sh.Rows(shLast)).Copy
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 bar
the bit at the top that doesn't work..

Hopefully someone can help...


--
tonyDeBrascoPosted from http://www.pcreview.co.uk/ newsgroup access

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


--
HTH

Bob Phillips

"tonyDeBrasco" <tonyDeBrasco.1skpry@ wrote in message
...

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

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

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

Also as I am new to Excel and VB I will attempt to provide comments
showing what I think all of the lines mean, if I am wrong please
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 be
the entire path eg C:// etc...???
sh = myVar //assigning the text entered by user as the name of the
worksheet


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

Application.ScreenUpdating = False //don't know
Set DestSh = ThisWorkbook.Worksheets.Add //adding a sheet to the
current workbook
DestSh.Name = "Overall Totals" //naming that new sheet
For Each sh In ThisWorkbook.Worksheets //I think ThisWorkbook
should be myVar??
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh) //last row = function to find
last row
shLast = LastRow(sh) //so we know how many rows to
copy
sh.Range(sh.Rows(5), sh.Rows(shLast)).Copy
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 bar
the bit at the top that doesn't work..

Hopefully someone can help...


--
tonyDeBrascoPosted from http://www.pcreview.co.uk/ newsgroup access

Thats true, the code I supplied only catered for one textbox, Im taking
baby steps. I figure if I can get it to pull data in from one sheet in
a particular work book it should be easy enough to get it do it for
others.

I copied your tidied up code and well it had unexpected results. I am
launching the form via a button on sheet 1 of the work book which will
contain the overall totals. After entering a complete path for a .xls
file it merely copied the button and placed it on a sheet called
"Overall Totals". So it is at least creating the destination sheet of
the data.

I looked over the code again and should the line

For Each sh In ThisWorkbook.Worksheets

not contain a reference to what was entered in the textbox, perhaps
something like

For Each sh In myVar.Worksheets

however I don't want it to take every sheet, only sheets named Totals,
how could I specify that, something like

For sh.Name == "Totals" in myVar.Worksheets

Also when I entered the line
For Each sh In myVar.Worksheets

it gave me the following error "compiler error: invalid qualifer" and
highlighted the myVar in the code? Is this because myVar is defined as
a string and not as a worksheet or something?

Thanks for your continuing help, solution is getting closer (I hope),
Tony


--
tonyDeBrascoPosted from http://www.pcreview.co.uk/ newsgroup access