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

[Adeptus - ExcelForums.com]

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

 

[Bunter_22]

Hi,

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

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 = ComboBox1.Text and
subsequently change the reference of the variable to ShtName =
Textbox4.text.

Any other problems then give me a shout.

James

 

[Adeptus - ExcelForums.com]

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.

 

[Bunter_22]

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

 

[Adeptus - ExcelForums.com]

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?

 

[Adeptus - ExcelForums.com]

I am really stumped.

It seems like every time I get a little further with it , 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.

 

[Bunter_22]

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 = Range("A400").­End(xlUp)

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

James

 

[Adeptus - ExcelForums.com]

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.

 

[Adeptus - ExcelForums.com]

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]

 

[Adeptus - ExcelForums.com]

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.

 

[Adeptus - ExcelForums.com]

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?

 

[Adeptus - ExcelForums.com]

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

 

[Bunter_22]

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

 

[Adeptus - ExcelForums.com]

Heya, After I posted that, I wrote this

Private Sub CheckBox1_Click(
CheckBox2 = Locke
CheckBox3 = Locke
CheckBox4 = Locke
CheckBox5 = Locke
CheckBox6 = Locke

End Su

Private Sub CheckBox2_Click(
CheckBox1 = Locke
CheckBox3 = Locke
CheckBox4 = Locke
CheckBox5 = Locke
CheckBox6 = Locke

End Su

Private Sub CheckBox3_Click(
CheckBox1 = Locke
CheckBox2 = Locke
CheckBox4 = Locke
CheckBox5 = Locke
CheckBox6 = Locke

End Su

Private Sub CheckBox4_Click(
CheckBox1 = Locke
CheckBox2 = Locke
CheckBox3 = Locke
CheckBox5 = Locke
CheckBox6 = Locke

End Su

Private Sub CheckBox5_Click(
CheckBox1 = Locke
CheckBox2 = Locke
CheckBox3 = Locke
CheckBox4 = Locke
CheckBox6 = Locke

End Su

Private Sub CheckBox6_Click(
CheckBox1 = Locke
CheckBox2 = Locke
CheckBox3 = Locke
CheckBox4 = Locke
CheckBox5 = Locke

End Su

Private Sub CommandButton66_Click(

Dim LastRow As Objec

If CheckBox1 = "True" The
Sheets("U14 Single").Selec
Els
GoTo
End I


If CheckBox2 = "True" The
Sheets("U14 Large").Selec
Els
GoTo
End I


If CheckBox3 = "True" The
Sheets("14-18 Single").Selec
Els
GoTo
End I


If CheckBox4 = "True" The
Sheets("14-18 Large").Selec
Els
GoTo
End I


If CheckBox5 = "True" The
Sheets("Open Single").Selec
Els
GoTo
End I


If CheckBox6 = "True" The
Sheets("Open Large").Selec
End I


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

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

MsgBox "Entry successfully written to Data Table

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

If response = vbYes The
Range("A" & Range("E3")
"C" & Range("E3")).Selec
Selection.Cop
Sheets("Printout").Selec
Range("M12").Selec
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Operation:=
xlNone, SkipBlanks:=False, Transpose:=Tru
Range("A1").Selec
Application.CutCopyMode = Fals
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Collate:=Tru
Sheets("U14 Single").Selec
Range("A5").Selec

MsgBox "Entry successfully printed!

End I

response = MsgBox("Do you want to input anothe
Entry?",
vbYesNo

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

TextBox1.SetFocu

Els
Unload M
End I

End Su


So it works beautifully!

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

 

[Bunter_22]

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