Help with VB script please?

[Peter]

Hi,

I'm having a little trouble with the following VB. What it's meant to
do is to load the contents of a text box and two combo boxes into
particular cells in a spreadsheet - This bit is OK. However, it is
also meant to copy the same data into three celss on a different
worksheet (one that is created when the form used is launched), but
what it does is copy the contents of the text box into all three
cells, ignoring what is in the combo boxes. Obviously something wrong
with my VB script, but cannot see exactly what.

Any suggestions would be much appreciated.

Private Sub CommandButton1_Click()

Dim rng As Range

Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
rng.Value = TextBox1
Selection.copy
Sheets("Form master (2)").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues
Sheets("Main").Select

Sheets("Main").Select
Set rng = Cells(Rows.Count, "B").End(xlUp)(2)
rng.Value = ComboBox1
Selection.copy
Sheets("Form master (2)").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlValues
Sheets("Main").Select

Set rng = Cells(Rows.Count, "C").End(xlUp)(2)
rng.Value = ComboBox2
Selection.copy
Sheets("Form master (2)").Select
Range("B11").Select
Selection.PasteSpecial Paste:=xlValues
Sheets("Form master (2)").Select
Sheets("Form master (2)").Name = (TextBox1.Text)


--
Cheers

Peter

Remove the INVALID to reply

 

[Bob Phillips]

Peter,

I think it is because you are using the Selection, not where you stored the
data.

Try this

Private Sub CommandButton1_Click()

Dim rng As Range

Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
rng.Value = TextBox1
rng.Copy
Sheets("Form master (2)").Range("B3").PasteSpecial Paste:=xlValues

Set rng = Sheets("Main").Cells(Rows.Count, "B").End(xlUp)(2)
rng.Value = ComboBox1
rng.Copy
Sheets("Form master (2)").Range("B7").PasteSpecial Paste:=xlValues

Set rng = Sheets("Main").Cells(Rows.Count, "C").End(xlUp)(2)
rng.Value = ComboBox2
rng.Copy
Sheets("Form master (2)").Range("B11").PasteSpecial Paste:=xlValues

Sheets("Form master (2)").Name = (TextBox1.Text)

End Sub

 

[Peter]

Peter,

I think it is because you are using the Selection, not where you stored the
data.

Try this

Private Sub CommandButton1_Click()

Dim rng As Range

Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
rng.Value = TextBox1
rng.Copy
Sheets("Form master (2)").Range("B3").PasteSpecial Paste:=xlValues

Set rng = Sheets("Main").Cells(Rows.Count, "B").End(xlUp)(2)
rng.Value = ComboBox1
rng.Copy
Sheets("Form master (2)").Range("B7").PasteSpecial Paste:=xlValues

Set rng = Sheets("Main").Cells(Rows.Count, "C").End(xlUp)(2)
rng.Value = ComboBox2
rng.Copy
Sheets("Form master (2)").Range("B11").PasteSpecial Paste:=xlValues

Sheets("Form master (2)").Name = (TextBox1.Text)

End Sub

Hi Bob,

Thanks very much for your reply - again it worked. But it only works
sometimes!

I am writing a spreadsheet for work which contins all my questions,
but I have a small spreadsheet that I try the various bits out in.

The following script works well - does exactly what I want

Dim rng As Range
Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
rng.Value = TextBox1
rng.copy
Sheets("Form master (2)").Range("B3").PasteSpecial Paste:=xlValues
Sheets("Main").Select
ActiveCell.Value = CDbl(TextBox1.Text)

However, when I put the code into my work spreadsheet I get the
following error

Run time error '9'
Subscript out of range

The script is as follows:

Dim rng As Range
'Sets AIR number
Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
rng.Value = TextBoxAIR
'Next copies the contents into A1 in formmaster worksheet
rng.Copy
Sheets("Form master(2)").Range("A1").PasteSpecial Paste:=xlValues
Sheets("Bude").Select
ActiveCell.Value = CDbl(TextBoxAIR.Text)

The line that is causing the problem and is highlighted is

Sheets("Form master(2)").Range("A1").PasteSpecial Paste:=xlValues

Grateful if you could suggest what is wrong


--
Cheers

Peter

Remove the INVALID to reply

 

[Bob Phillips]

That suggests to me that you don't have a worksheet called Form master(2 in
the activeworkbook. Check the exact name.

 

[Peter]

That suggests to me that you don't have a worksheet called Form master(2 in
the activeworkbook. Check the exact name.

Hi Bob,

Thanks for your reply.

I have checked the name and just to make sure that there wasn't an
additional space etc I reduced the name down to "Form" - no change - I
still get the runtime error message.

I think I may have to rewrite the whole thing to try and get around
this.


--
Cheers

Peter

Remove the INVALID to reply

 

[Peter]

That suggests to me that you don't have a worksheet called Form master(2 in
the activeworkbook. Check the exact name.

Here's one for the book!

I changed the name from "Form master" to "Form" - no joy, still the
same runtime error.

Changed the name of the worksheet to "Peter" and the associated bits
of script in the macro - it works like a dream.

I have a macro which launches the form - Openairform.Show - I wonder
if the common word Form was screwing it up?

Still, it's sorted now - on to the next stage,


--
Cheers

Peter

Remove the INVALID to reply