Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VB script please?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VB script please?
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 -- HTH RP "Peter" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VB script please?
On Tue, 12 Oct 2004 19:33:58 +0100, "Bob Phillips"
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VB script please?
That suggests to me that you don't have a worksheet called Form master(2 in
the activeworkbook. Check the exact name. -- HTH RP "Peter" wrote in message ... On Tue, 12 Oct 2004 19:33:58 +0100, "Bob Phillips" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VB script please?
On Wed, 13 Oct 2004 22:25:31 +0100, "Bob Phillips"
wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VB script please?
On Wed, 13 Oct 2004 22:25:31 +0100, "Bob Phillips"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with vba script | New Users to Excel | |||
Help ! How do I do this in VB Script | Setting up and Configuration of Excel | |||
I need some VB script please | Excel Discussion (Misc queries) | |||
VB script help - please!! | Excel Discussion (Misc queries) | |||
Excel 2000/XP script to Excel97 script | Excel Programming |