VB code help needed

[Phil]

I'm not a programmer, but with the help of this group I can usually figure
it out, but this time I stuck. Could someone please help me with writing the
vb code for the following situation?

I have two excel files: excelfile1.xls and excelfile2.xls

In excelfile1.xls I have a button that when clicked opens a form that has a
field to enter a name. You enter the name you want and click an ok button.
It then creates a new sheet in both excel files with the name you entered.
(example: you enter test1 as your name and a new sheet is created in both
excel files called test1). Works fine.

I then have another button in excelfile1.xls that gets created on the test1
sheet that when clicked opens a form with 3 fields. Game1, Game2, Game3. I
enter values in these text boxes and click ok.
When I do this I need the values to be copied to excelfile2.xls into cell
c4,c5,c6 on the sheet test1.
The catch is I could create test1 sheet today, test2 sheet tomorrow, test3
sheet the next day, etc. When I enter the values in the form I need them to
be copied to the same sheet name in excelfile2.xls. (example: test1 and
test2 sheet gets created in excelfile1.xls and excelfile2.xls. I am on sheet
test1 in excelfile1.xls, I click the button and enter values in the form. I
need the values to get entered in cell c4,c5,c6 on sheet test1 in
excelfile2.xls. Then I'm on sheet test2 in excelfile1.xls, enter my values
in the form, I need them to get entered into c4,c5,c6 on sheet test2 in
excelfile2.xls)

So basicly I need the values from the form in excelfile1.xls to get entered
onto the sheet with the same name in excelfile2.xls.
I hope this isn't too confusing. If you need more info from me let me know.
Thanks for any coding help you can supply.

Phil

 

[HS Hartkamp]

Phil,

If I understand the question correctly, I think you'd need ActiveSheet.Name
somewhere in your code.

e.g.
Workbooks("Excelfile2").Sheets(ActiveSheet.Name).Range("C4").Value =
<value1>
Workbooks("Excelfile2").Sheets(ActiveSheet.Name).Range("C5").Value =
<value2>
Workbooks("Excelfile2").Sheets(ActiveSheet.Name).Range("C6").Value =
<value3>

The trick would be that the moment you click the button, the sheet
containing the button is active, and therefore you can ask and use its name.
If your code shifts focus to another sheet (by selecting other ranges), make
sure you query the name before you shift focus. To do this, start your code
with
Dim strDestinationSheet as String
strDestinationSheet = ActiveSheet.Name (somewhere in the beginning of the
code)

and then use
Workbooks("Excelfile2").Sheets(strDestinationSheet).Range("C4").Value =
<value1> etc.

Bas Hartkamp.

 

[Phil]

This looks like what I'm looking for. I need to learn more on the use of
Dim. Then I'll be able to figure more of this code out.
Thanks alot, I'll give it a try.

Phil

 

[HS Hartkamp]

It's not about dim, it's about proper use of variables (do some google
searches on that !). DIM itself is very easy.

All variables you use in your code can be of various types (integer numbers,
text strings, real numbers, boolean values, but also more complex objects
such as ranges, worksheets, forms, or even the generic ' object '). To make
sure the computer treats your variables right, you declare them at the start
of your code. For this, the command keyword DIM is used. See help files for
details.

If you do not declare them in advance, the computer makes a guess, based on
the first time one is used. This is often right, but not always. Especially
if you misspel the variable and end up having two (nearly identical)
variables. Very confusing stuff to find that error.
To avoid this, the words OPTION EXPLICIT are used on the top of the (code)
page. This forces the code into that all variables should be declared in
advance (using DIM). The computer tells you when a variable has not been
declared properly.

To use this option explicit is good practice. It is a great aid to any
programmer into working neat and methodically. That, in turn, makes for good
programs with less errors that are much easier to read and understand.

Bas.