Same Code, Different Cells

  • Thread starter Thread starter Craig Coope
  • Start date Start date
C

Craig Coope

I have just coded a user interface in the guise of a few forms for a
spreadsheet I've created.

The spreadsheet has the same table duplicated 27 times all the way
down it but I have only coded the forms (user interface) to work for
the first table on my spreadsheet and it took ages to give
instructions for each cell etc.

Is there anyway I can duplicate my code for the rest of my tables (in
the same sheet) easily? ie can I duplicate the code but change the
cells that are referred to in that code to suit the other tables? I
know I can copy and paste but I'd still have to go through it all and
change the cell numbers. I was hoping to just use one cell as a start
ref point and the other cell refs will automatically change.

Let me show a grab of my sheet
http://www.geocities.com/coope1999/excel1.jpg

Notice I'd like to use for example cell b40 as the ref point for table
one and b76 as the ref for table 2 and so on.

Any ideas?

Cheers....

Craig...
 
Craig,

You can give each table a name (menu Insert>Name; i've the Dutch version and
don't know the exact menu names in the English version) and refere to the
cells within the table relative to the table:

say Range("A39:K69") is called Table_01
then you can refere to A39 with
Range("Table_01").Range("A1")

so only changing the name of the table will do.

You can use a combobox (cboTableNumber) with the numbers 1 to 27
choose a number and you can refere to Range("A1") within the chosen table
with:

Range("Table_" & Me.cboTableNumber).Range("A1")

Hope this will do the job for you.l

Jan
 
Craig,

An other possibillity is more like you suggested: "I was hoping to just use
one cell as a start
ref point and the other cell refs will automatically change"

Range("A39").Range("A1") refers to Range("A39")
Range("A39").Range("C2") refers to Range("C41")

So, use a reference-cell for each table instead of giving a name to each
table.

You could fill the combobox I mentioned in my previous message, with the
addresses of the reference-cells for each table or still fill it with the
numbers 1 to 27 and make a translation to the reference-cells within the
code.

Jan
 
Craig,

An other possibillity is more like you suggested: "I was hoping to just use
one cell as a start
ref point and the other cell refs will automatically change"

Range("A39").Range("A1") refers to Range("A39")
Range("A39").Range("C2") refers to Range("C41")

So, use a reference-cell for each table instead of giving a name to each
table.

You could fill the combobox I mentioned in my previous message, with the
addresses of the reference-cells for each table or still fill it with the
numbers 1 to 27 and make a translation to the reference-cells within the
code.

Jan
Click to expand...

Thanks for the advice Jan but I am still struggling to get my head
around it. I'm going to paste you a bit of code.


If ToggleButton1.Value = True And ComboBox1 = "HIN" And Range("b40") =
0 Then
Range("b40") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" And
Range("b40") > 0 And Range("b46") = 0 Then
Range("b46") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" And
Range("b46") > 0 And Range("b52") = 0 Then
Range("b52") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" And
Range("b52") > 0 And Range("b58") = 0 Then
Range("b58") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" And
Range("b58") > 0 And Range("b64") = 0 Then
Range("b64") = Time()
End If

Now you don't have to understand what the code does, I just want to
give my example. This is a very small part of the code but for the
second table in my sheet it would look exactly the same apart from the
cells would be something like b76 instead of b40 and b82 instead of
b46, but the amount differences between the cells will always be the
same. I hope that makes sense.

So somehow I want to capture the above code by somehow putting
brackets around the code and then naming it code1 or something like
that. I'm sure that is not how it is done but I really don't know how
to do it.

Then hopefully write a simple line suchas

table2 = code1 (but with different cell numbers.) I wish it was that
easy.

Hope you can help further...

Thanks again,

Craig...
 
Craig

For the first table you can use:

With Worksheet("Sheets1").Range("A39")
If ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b2") = 0 Then
.Range("b2") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b2") > 0 And .Range("b8") = 0 Then
.Range("b8") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b8") > 0 And .Range("b14") = 0 Then
.Range("b14") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b14") > 0 And .Range("b20") = 0 Then
Range("b20") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b20") > 0 And .Range("b26") = 0 Then
.Range("b26") = Time()
End If
End With

So changing all ranges by subtracting 38 and put a dot in front of the word
Range.

The second table you could access with the same code but with Range("A75")
as reference:

With Worksheet("Sheets1").Range("A75")
If ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b2") = 0 Then
.Range("b2") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b2") > 0 And .Range("b8") = 0 Then
.Range("b8") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b8") > 0 And .Range("b14") = 0 Then
.Range("b14") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b14") > 0 And .Range("b20") = 0 Then
Range("b20") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b20") > 0 And .Range("b26") = 0 Then
.Range("b26") = Time()
End If
End With

etc.

But it would be nicer if you had a combobox (cboRef) in your form with the
addresses of the reference ranges to choose.
A39
A75
etc.

and then write the code:

With Worksheet("Sheets1").Range(Me.cboRef)
If ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b2") = 0 Then
.Range("b2") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b2") > 0 And .Range("b8") = 0 Then
.Range("b8") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b8") > 0 And .Range("b14") = 0 Then
.Range("b14") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b14") > 0 And .Range("b20") = 0 Then
Range("b20") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b20") > 0 And .Range("b26") = 0 Then
.Range("b26") = Time()
End If
End With

Depending on the choice in this combobox your Userform writes/reads the
corresponding table.

I hope this explains things a bit.

Jan
 
Craig,

Of course I made a mistake:

Range("b20") = Time() misses the dot and should be:
..Range("b20") = Time()

Jan
 
Craig,

Of course I made a mistake:

Range("b20") = Time() misses the dot and should be:
.Range("b20") = Time()

Jan
Click to expand...

Cheers Jan...I understand it now I think...

I'll give it a try when I have a spare minute.

Craig...
 
----- Original Message -----
From: "jan" <[email protected]>
Newsgroups: microsoft.public.excel
Sent: Monday, March 19, 2007 10:18 PM
Subject: Re: Same Code, Different Cells
But it would be nicer if you had a combobox (cboRef) in your form with the
addresses of the reference ranges to choose.
A39
A75
etc.

and then write the code:

With Worksheet("Sheets1").Range(Me.cboRef)
If ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b2") = 0 Then
.Range("b2") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b2") > 0 And .Range("b8") = 0 Then
.Range("b8") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b8") > 0 And .Range("b14") = 0 Then
.Range("b14") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b14") > 0 And .Range("b20") = 0 Then
Range("b20") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b20") > 0 And .Range("b26") = 0 Then
.Range("b26") = Time()
End If
End With

Depending on the choice in this combobox your Userform writes/reads the
corresponding table.

I hope this explains things a bit.

Jan
Click to expand...

Thanks again for your help Jan. I got the first example to work but like you
said using a combobox would me much better as it would mean I would have to
store less code. I've created the combobox on a different form and have
tried several different ways to write the syntax but I can't get it to go..

I have

With Sheet1.Range(UserForm1.ComboBox1.Value)
If ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b2") = 0 Then
.Range("b2") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b2") > 0 And .Range("b8") = 0 Then
.Range("b8") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b8") > 0 And .Range("b14") = 0 Then
.Range("b14") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b14") > 0 And .Range("b20") = 0 Then
.Range("b20") = Time()
ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
And .Range("b20") > 0 And .Range("b26") = 0 Then
.Range("b26") = Time()
End If
End With

It is just the first line of code I need help with. I've put in the address
of my combobox (which will have the ref a39 already in it) but I'm getting
errors. What is the correct syntax to refer to the ref in my combobox?

I hope that that is all I need to know :o)

Craig...
 
Craig,

With Sheet1.Range(UserForm1.ComboBox1.Value)

should be something like:

With Worksheet("Sheets1").Range(UserForm1.ComboBox1.Value)

and the property ShowModal from UserForm1 should be False.

But why not have the combobox with the ranges on the same UserForm.
I think its much easyer to use and I don't sea the advantages of the second
Userform.

Jan
 
jan said:
Craig,

With Sheet1.Range(UserForm1.ComboBox1.Value)

should be something like:

With Worksheet("Sheets1").Range(UserForm1.ComboBox1.Value)

and the property ShowModal from UserForm1 should be False.

But why not have the combobox with the ranges on the same UserForm.
I think its much easyer to use and I don't sea the advantages of the second
Userform.
Click to expand...

Sorry Jan...I still get the same error message..

I have moved my cmbo to the same form and have got the following

With Worksheet.("Sheet1").Range(Me.ComboBox2.Value)

I have tried changing sheet1 to sheets1 and the same with worksheet but I
get object not defined or subfunction out of range....but I'm sure that the
latter half of the code line is correct ".Range(Me.ComboBox2.Value)"
It is called combobox 2 now because it is the second on that form.

With your code after I have changed the address of my cmbo I get

With Worksheet("Sheets1").Range(Me.ComboBox2.Value)

but when I run the code it says function or subfunction not defined and it
highlights "Worksheet" in blue so I assume there is something wrong with
that part of the address...I have not changed the names of any of my sheets
in Excel...

Any ideas?

Thanks again for your time....
 
Craig,

It's a little mistake:

In

With Worksheet("Sheets1").Range(Me.ComboBox2.Value)

the s after Worksheet is missing (I gave it to you in this way and that
wasn't right)

it has to be:

With Worksheets("Sheets1").Range(Me.ComboBox2.Value)

where Sheets1 is the name of the sheet (or it has to be Sheet1?)

Jan
 
jan said:
Craig,

It's a little mistake:

In

With Worksheet("Sheets1").Range(Me.ComboBox2.Value)

the s after Worksheet is missing (I gave it to you in this way and that
wasn't right)

it has to be:

With Worksheets("Sheets1").Range(Me.ComboBox2.Value)

where Sheets1 is the name of the sheet (or it has to be Sheet1?)

Jan
Click to expand...

Thanks for all your help Jan, Looks like I have all I need to finish my
project....
 
Back
Top