Call Sub Problem

[Kavy]

Hello, I am using a user Form in excel VB

I have a bunch of text boxes on another form. What set of text boxes i use depend on what illertation I am on, "counter". I am calling the sub which uses the "Select Case" code to figure out which textbox to use.

When I run it, it reaches the call, goes into the sub, and when it exits the sub it screws up. It says "Type miss match"

Intresting note, if you click play again, it works right away untill it runs another illertation through and gets back to that spot.

In the call function in the code below, all variables have Nothing in them when I put my mouse over them in debug. When I say nothing it actually says like textboxy = Nothing

counter has 1 to start.

Here is my code if anyone can help, thanks for any help at all!

Call subcase(textboxy, textboxM, textboxMa, textboxw, textboxd, checkboxx, counter)

and here is the sub! thanks again!

 Sub subcase(textboxy, textboxM, textboxMa, textboxw, textboxd, checkboxx, counter)

Select Case counter
                
                Case 1
                Set textboxy = frmNewItemMore.txtYear1
                Set textboxM = frmNewItemMore.txtMonth1
                Set textboxw = frmNewItemMore.txtweek1
                Set textboxd = frmNewItemMore.txtDay1
                Set textboxMa = frmNewItemMore.txtPM1
                Set checkboxx = frmNewItemMore.chk1
                Case 2
                Set textboxy = frmNewItemMore.txtYear2
                Set textboxM = frmNewItemMore.txtMonth2
                Set textboxw = frmNewItemMore.txtweek2
                Set textboxd = frmNewItemMore.txtDay2
                Set textboxMa = frmNewItemMore.txtPM2
                Set checkboxx = frmNewItemMore.chk2
                Case 3
                Set textboxy = frmNewItemMore.txtYear3
                Set textboxM = frmNewItemMore.txtMonth3
                Set textboxw = frmNewItemMore.txtweek3
                Set textboxd = frmNewItemMore.txtDay3
                Set textboxMa = frmNewItemMore.txtPM3
                Set checkboxx = frmNewItemMore.chk3
                Case 4
                Set textboxy = frmNewItemMore.txtYear4
                Set textboxM = frmNewItemMore.txtMonth4
                Set textboxw = frmNewItemMore.txtweek4
                Set textboxd = frmNewItemMore.txtDay4
                Set textboxMa = frmNewItemMore.txtPM4
                Set checkboxx = frmNewItemMore.chk4
                Case 5
                Set textboxy = frmNewItemMore.txtYear5
                Set textboxM = frmNewItemMore.txtMonth5
                Set textboxw = frmNewItemMore.txtweek5
                Set textboxd = frmNewItemMore.txtDay5
                Set textboxMa = frmNewItemMore.txtPM5
                Set checkboxx = frmNewItemMore.chk5
                Case 6
                Set textboxy = frmNewItemMore.txtYear6
                Set textboxM = frmNewItemMore.txtMonth6
                Set textboxw = frmNewItemMore.txtweek6
                Set textboxd = frmNewItemMore.txtDay6
                Set textboxMa = frmNewItemMore.txtPM6
                Set checkboxx = frmNewItemMore.chk6
                Case 7
                Set textboxy = frmNewItemMore.txtYear7
                Set textboxM = frmNewItemMore.txtMonth7
                Set textboxw = frmNewItemMore.txtweek7
                Set textboxd = frmNewItemMore.txtDay7
                Set textboxMa = frmNewItemMore.txtPM7
                Set checkboxx = frmNewItemMore.chk7
    End Select
End Sub

 

[Norie]

Why do you need that sub in the first place?

Set textboxy = frmNewItemMore.Controls("txtYear" & Counter) 
Set textboxM = frmNewItemMore.Controls("txtMonth"  & Counter)
etc

I'm also wondering why you are using Set.

What are you doing with these textboxes?

PS Ever considered a listbox rather than multiple texboxes.

 

[Kavy]

ah it yelled at me when i wasn't using set, so i tried set and it stoped getting mad at that point.

The textboxes are made for frequancy of work.

one is for years, one for months, weeks, days

That would all be one counter
the next one would be a new frequancy

So they can say a some work accours every 0 years, 3 days

I can't use list boxes cause the list would be infinitly long

 

[Norie]

I can't use list boxes cause the list would be infinitly long

How?Why?

 

[Kavy]

Well I don't get how a list would work, the textbox data gets brought in from somewhere else, if it exist yet. And then the user can change it.
To have a listbox it would be infitily long because you would need between 1 and infitly years (granted nothing will praobly be over a year but still)

I don't get how a list box owuld be easier in this case though?

 

[Norie]

Sorry you've lost me again.

Where are you getting infinity from?

Also if you are dealing with dynamic data I would think it would be easier to use a listbox than multiple textboxes.

For example the code you posted refers to 6 sets of textboxes, what happens if there's more than 6 sets of data?

 

[Kavy]

If theres more then the 7 (same thing) sets of data, the user clicks ok , where there prompt if they want to enter more, if they do, the data is saved and the textboxes are cleared (another problem of mine, clearing all 28 text boxes without typing them all, someone else posted code but i haven't gotten it working)

I am going to try to find a photo site so i can post a link of the form, might be easier to grasp cause I struggle at explaing things sometimes

 

[Norie]

Kavy

I think you might want to rethink your approach here.

What about this?

1 listbox, one set of textboxes and a command button.

User enters data in textboxes, hits command buttton and the data is transferred to the listbox.

You could also add other functionality.

For example user selects item in listbox, data appears in textboxes, user can alter data as needed and then data transferred back to listbox.

Another example - user selects item in listbox, hits button and the data is deleted.

All of this is pretty much theory/speculation as I don't actually know what you are trying to achieve.

 

[Kavy]

This link is the form which will load after the command this code is in loads it (very end of command). the textboxes are on this form.

I should also note the variables textboxy and textboxw etc are
Dim textboxy as textbox
and the checkbox is dim checkboxx as checkbox

Here is the link

http://s205.photobucket.com/albums/bb163/kavie_bucket/

 

[Kavy]

I do like the listbox idea, with only one row of textboxes, so like another multicol listbox, which thanks to you I no how to do