++HELP ME PLEASE++ ComboBoxes as a Variable to Select Sheets

Good Morning,

I'm starting to fumble my way through VB by tinkering and trying new
things with each project I set myself to. I'm currently working on a
detail logging form for competition entry. This is how it needs to
pan out:

Picture this, if you will...

A main sheet, with a "Data Entry Button".

When clicked, this button shows a userform with a few fields; Name,
Entry Name, Ticket Number and Category.

The first three are TextBoxes and work fine, no problems at all. I
can get them to copy into a worksheet and all that. HOWEVER!

The Problem comes when I introduce a separate WorkSheet for each
category. What I have is a ComboBox in the UserForm with the list of
Categories, which are the same as the sheet names. What I want is for
the user to be able to select a category in the ComboBox, and have it
copy the details from the 3 text boxes into the selected category's
sheet.

I assume this is simply a case of finding how to get the text from
within the ComboBox to be the name of the worksheet to select before
copying, however I cannot find out how.

Help on this would be splendid.

Here is the code so-far. The SHEETNAME is where the variable would go
I immagine, but I need to be able to set the Text from the ComboBox as
the source for that sheet name.

===============================================

Private Sub CommandButton66_Click()


Dim LastRow As Object

Set LastRow = SHEETNAME.Range("a400").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text

MsgBox "Entry successfully written to Data Table"

response = MsgBox("Do you want to print the Entry Certificate
now?", vbYesNo)

If response = vbYes Then
Range("A" & Range("E3"), "C" & Range("E3")).Select
Selection.Copy
Sheets("Printout").Select
Range("M12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
ActiveSheet.Shapes("Picture 1").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("U14 Single").Select
Range("A5").Select

MsgBox "Entry successfully printed!"

Else
Unload Me
End If

response = MsgBox("Do you want to input another Entry?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
ComboBox1.Text = ""

TextBox1.SetFocus

Else
Unload Me
End If

End Sub

Hi,

At the top of your code:
Replace:
Dim LastRow As Object
Set LastRow =3D SHEETNAME.Range("a400").End(xl=ADUp)
With:
Dim LastRow as Integer
ShtName =3D combobox1.Text
Sheets(ShtName).Select
LastRow =3D Range("a400").End(xl=ADUp).row

That should do the trick. If that doesn't work, then a work around
would be to put a hidden textbox on the userform, then the onchange
event of the combo box put that TextBox4.text =3D ComboBox1.Text and
subsequently change the reference of the variable to ShtName =3D
Textbox4.text.

Any other problems then give me a shout.

James

>
> At the top of your code:
> Replace:
> Dim LastRow As Object
> Set LastRow = SHEETNAME.Range("a400").End(xl*Up)
> With:
> Dim LastRow as Integer
> ShtName = combobox1.Text
> Sheets(ShtName).Select
> LastRow = Range("a400").End(xl*Up).row
>

This I have done, and upon reading it I see it's just what I was
hunting for.... however now an unforeseen error has cropped up, when
I run it now, it brings up an "Invalid Qualifier" error, and
highlights the first "LastRow" of this bit.

>
> LastRow.Offset(1, 0).Value = TextBox1.Text
> LastRow.Offset(1, 1).Value = TextBox2.Text
> LastRow.Offset(1, 2).Value = TextBox3.Text
>

Hi,

Sorry, I wasn't thinking straight. do a quick change (More or less what
you had!).

Dim LastRow As Object
ShtName = combobox1.Text
Set LastRow = Sheets(ShtName).Range("A400").End(xlUp)

The reason it was an invalid qualifier is that until you set lastrow it
won't have any properties.

James

>
> Dim LastRow As Object
> ShtName = ComboBox66.Text
>
> Set LastRow = Sheets(ShtName).Range("A400").End(xlUp)
>
> LastRow.Offset(1, 0).Value = TextBox1.Text
> LastRow.Offset(1, 1).Value = TextBox2.Text
> LastRow.Offset(1, 2).Value = TextBox3.Text
>

the above is what I now have...
but now I get "Runtime error '9' " - Script out of Range, and the
yellow bar of hate comes up on the Set LastRow line... The little
debugger boxes that come up when you hover are telling me that all
the stuff you've given so far is kicking along and working really
well... what's up now?

I am really stumped.

It seems like every time I get a little further with it :D , I just
come up against another error I don't understand. :x I have that
feeling like this should be really simple 8) , but I can't work it
out .

Please put me out of this frustration! :evil:

Much Obliged all. :D

Hi,

Subscript out of range means that the spreadsheet can't find the sheet,
or it doesn't understand the way it which the sheet is being called.
How is the combobox loaded? do you loop though adding the sheet names
or do you manually add them? Other than that you could split the "Set
Lastrow..." Into:

Sheets(ShtName).select
Set LastRow =3D Range("A400").=ADEnd(xlUp)

If you still can't get it to work then feel free to email it and I will
take a look.

James

The ComboBox references a list in one of my sheets that the sheet
names are written into.

I don't know how to call the sheet names directly.

I'm going to try that idea right away though.

OK, well it worked a treat... turns out that I was trying to call the
actual sheet names (The one at the top of the properties box in VB)
and not the Name on the Tab! SO when I tried that it's working
perfectly.

However now I have an interesting thing pop up,

When I run through the steps, if I select NOT to print and go straight
to entering a new entry, it has an error that says it cannot set focus
on the textbox1.

Runtime error 2110, can't move focus to the control because it is
invisible, not enabled, or of a type that does not accept focus.
Then it highlights the setfocus line. What's going on there?

following is the code:


Private Sub CommandButton66_Click()

Dim LastRow As Object
ShtName = ComboBox66.Text

Sheets(ShtName).Select

Set LastRow = Range("A400").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text

MsgBox "Entry successfully written to Data Table"

response = MsgBox("Do you want to print the Entry
Certificate now?", vbYesNo)

If response = vbYes Then
Range("A" & Range("E3"),
"C" & Range("E3")).Select
Selection.Copy
Sheets("Printout").Select
Range("M12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Range("A1").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Sheets("U14 Single").Select
Range("A5").Select

MsgBox "Entry successfully printed!"

Else
Unload Me
End If

response = MsgBox("Do you want to input another
Entry?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""

TextBox1.SetFocus

Else
Unload Me
End If

End Sub
[/code]

Ok so what I've done is remove the "Else Unload ME" from after
the print message box, and that seems to have done the trick.

OK, the only problem is that often I'm getting an "Exception Occurred"
error, this seems to be because the ComboBox doesn't do the text
properly or unselect it or whatever to make it useable. Is there a
way to stop it?

Here is a new code I have written.

What I'd like is to know if there's some code that I can put in to
make all checkboxes = false, or ensure that you can't have multiple
checkboxes checked.



Private Sub CommandButton66_Click()

Dim LastRow As Object

If CheckBox1 = "True" Then

Sheets("U14 Single").Select
Else
GoTo 2
End If

2
If CheckBox2 = "True" Then

Sheets("U14 Large").Select
Else
GoTo 3
End If

3
If CheckBox3 = "True" Then

Sheets("14-18 Single").Select
Else
GoTo 4
End If

4
If CheckBox4 = "True" Then

Sheets("14-18 Large").Select
Else
GoTo 5
End If

5
If CheckBox5 = "True" Then

Sheets("Open Single").Select
Else
GoTo 6
End If

6
If CheckBox6 = "True" Then

Sheets("Open Large").Select

End If


Set LastRow = Range("A400").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text

MsgBox "Entry successfully written to Data Table"

response = MsgBox("Do you want to print the Entry
Certificate now?", vbYesNo)

If response = vbYes Then
Range("A" & Range("E3"),
"C" & Range("E3")).Select
Selection.Copy
Sheets("Printout").Select
Range("M12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Range("A1").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Sheets("U14 Single").Select
Range("A5").Select

MsgBox "Entry successfully printed!"

End If

response = MsgBox("Do you want to input another
Entry?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""

TextBox1.SetFocus

Else
Unload Me
End If

End Sub

Hi,

Two things, firstly to only allow one control to be checked you need to
change the control to option buttons instead of check boxs, then you
can put these option buttons within a frame to allow for only one to be
checked, an alternative to a frame is to group the option buttons
(select all the buttons and then right click then group), either should
work.

Secondly I would rewrite your code above to a select case statement,
this will make things somewhat easier. The code below is an example of
what to use:

Select Case True
Case OptionButton1.Value
Sheets("U14 Single").Select
Case OptionButton2.Value
Sheets("U14 Large").Select
Case OptionButton3.Value
Sheets("14-18 Single").Select
Case OptionButton4.Value
Sheets("14-18 Large").Select
Case OptionButton5.Value
Sheets("Open Single").Select
Case OptionButton6.Value
Sheets("Open Large").Select
End Select

What this says is find the case where the optionbutton is true hence
select case true.
Then case optionbutton1.value is false it will automatically move to
case 2 and so on...

Any problems then give me a shout.

James

Heya, After I posted that, I wrote this:


Private Sub CheckBox1_Click()
CheckBox2 = Locked
CheckBox3 = Locked
CheckBox4 = Locked
CheckBox5 = Locked
CheckBox6 = Locked

End Sub

Private Sub CheckBox2_Click()
CheckBox1 = Locked
CheckBox3 = Locked
CheckBox4 = Locked
CheckBox5 = Locked
CheckBox6 = Locked

End Sub

Private Sub CheckBox3_Click()
CheckBox1 = Locked
CheckBox2 = Locked
CheckBox4 = Locked
CheckBox5 = Locked
CheckBox6 = Locked

End Sub

Private Sub CheckBox4_Click()
CheckBox1 = Locked
CheckBox2 = Locked
CheckBox3 = Locked
CheckBox5 = Locked
CheckBox6 = Locked

End Sub

Private Sub CheckBox5_Click()
CheckBox1 = Locked
CheckBox2 = Locked
CheckBox3 = Locked
CheckBox4 = Locked
CheckBox6 = Locked

End Sub

Private Sub CheckBox6_Click()
CheckBox1 = Locked
CheckBox2 = Locked
CheckBox3 = Locked
CheckBox4 = Locked
CheckBox5 = Locked

End Sub

Private Sub CommandButton66_Click()

Dim LastRow As Object

If CheckBox1 = "True" Then
Sheets("U14 Single").Select
Else
GoTo 2
End If

2
If CheckBox2 = "True" Then
Sheets("U14 Large").Select
Else
GoTo 3
End If

3
If CheckBox3 = "True" Then
Sheets("14-18 Single").Select
Else
GoTo 4
End If

4
If CheckBox4 = "True" Then
Sheets("14-18 Large").Select
Else
GoTo 5
End If

5
If CheckBox5 = "True" Then
Sheets("Open Single").Select
Else
GoTo 6
End If

6
If CheckBox6 = "True" Then
Sheets("Open Large").Select
End If


Set LastRow = Range("A400").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text

MsgBox "Entry successfully written to Data Table"

response = MsgBox("Do you want to print the Entry
Certificate now?", vbYesNo)

If response = vbYes Then
Range("A" & Range("E3"),
"C" & Range("E3")).Select
Selection.Copy
Sheets("Printout").Select
Range("M12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Range("A1").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Sheets("U14 Single").Select
Range("A5").Select

MsgBox "Entry successfully printed!"

End If

response = MsgBox("Do you want to input another
Entry?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
CheckBox1 = False
CheckBox2 = False
CheckBox3 = False
CheckBox4 = False
CheckBox5 = False
CheckBox6 = False

TextBox1.SetFocus

Else
Unload Me
End If

End Sub



So it works beautifully!

Is it long winded and convoluted?
How does a frame work and how do I go about putting option buttons in
it?

Hi,

I suppose I am one of those if it works then why it fix it type of
people, however option buttons are just the round buttons opposed to
the square ones. You would set them up just as you have set up your
check boxes.

A frame just collect the objects with in it, you basically draw a box
using the frame control and then drop your option buttons with in it,
this tells VBA that they are part of the same question or options. To
make things easier you don't have to use a frame, you can just group
the items instead. highlight the ones you want right click and hit
group similar to how you would group drawing items.

I would say its long winded, what you have done is write a work around,
when a solution exists. And again I would opt for a select case
statement instead of your other work around of the many if statements
combined with goto's. Look at it another way and you are using approx.
90 lines of code with in 8/9 sub routines when 14 lines of code with in
1 sub routine will do, stability and speed could be an issue, this is
something though you would have to decide, for example are you the
only one to use this etc.

Anything else then give me a shout.

James