Form data not going to correct date tab

[vbanewbie2]

When the user enters a date in the date field (txtbox) the data seems to be going to only one date tab (current month). I need the data to go into the correct month tab. another words if the data being input is from 3/1/2010 i need it to go to worksheet named MAR2010. If the data is from 1/1/2010 I need it to go to worksheet named JAN2010. any ideas?

[Guido Geurs]

Split the month from the text with:

Month = Left(TextBox1.Text, InStr(TextBox1.Text, "/") - 1)

Set the sheet name with:

      Select Case Month
      Case "1"
         SheetName = "JAN2010"
      Case "2"
         SheetName = "FEB2010"
      Case "3"
         SheetName = "MAR2010"
....
....
      End Select

Send the data to that sheet (= SheetName)

Like this=

Private Sub CommandButton1_Click()
Dim Month As String
Dim SheetName As String
   If IsDate(TextBox1.Text) Then
      Month = Left(TextBox1.Text, InStr(TextBox1.Text, "/") - 1)
      Select Case Month
      Case "1"
         SheetName = "JAN2010"
      Case "2"
         SheetName = "FEB2010"
      Case "3"
         SheetName = "MAR2010"
      End Select
      Worksheets(SheetName).Range("A1").Value = TextBox1.Text
   Else
      MsgBox "Enter a date"
   End If
End Sub

[vbanewbie2]

@ggeu

I noticed that you are using range("A1") but all my data gets added to a new row each time the user hits the enter key onb the user form. u have seen my project. how would i get to work fo me? same thing for the Printing a receipt problem. you are using a set cell. but if the client the user is looking for is in 151 how will that work?

[vbanewbie2]

sorry GGeu - not working ..... probably me.

[Guido Geurs]

Is this the same project as "printing a receipt from excel" ?
Please, attach an example of Your project in BYTES so we can help You based on the real problem and simulate the errors so we can find a solution for it!

[vbanewbie2]

ok GGEU, here it is again, I was told it was too busy and had to remove a few things. the things I was told to work on are the following:
1) i need any data that is entered into the form to go into the correct worksheet tab (i.e. if the date entered into the txtscheddate field is 1/1/2010 it will go into worksheet JAN2010.

2). I need a way that the user can print out a receipt. no matter who she is looking for. If the receipt is from Jan2010 and the user is liz martin or if the receipt was from yesterday and the user was kate blanchard. (this is a tough one).

3). My search code seems to be only searching in the worksheet of Jan2010 and not all of them, plus it only brings up the first occurrence of said said. I need it to bring up all occurrences for all twelve months.

I have added the zip file so you can take a look at it and tell me what you think. Oh waiti there is one more item. when the user first opens the userform and starts to type in data when she gets to the amount fields and presses' the CTRL & TAB button to get to the totmaterial txtbox it sometimes does not work. any ides why that is.

By the way if I have not thanked you before in the past Let express my deepest gratitude to you and everybody on this site. I have been to mr. excel and wopr.com and they seem as if they don't care about us newbies trying to make it inb the vba world. so thank yo very much.

[Guido Geurs]

Attached is what I have so far.
I don't think we can use CTRL+TAB to jump to Total because it enters a TAB in the starting textbox or in the end textbox.
So I have changed it to SHIFT+TAB and it's working.

I have added a Menu + button to start the Add form.

Next is the search and print code.

[Guido Geurs]

I have been thinking about the problem "search" and have the next observations:

- If we want to search on the name of a client, these name must be the same in each data of that client.
What it means: if a user must enter the name by TYPING than he will enter "k. blanchard" or "kate b." or "kate blancharde" or ... for "kate blanchard" and the search will never find all the data.

- So we have to standard the names of the clients!
This is done by using a list on a sheet "Clients".

- This sheet has a lot of advantages !!

- This list can also be used for other things like: placing a listbox on the Add form in which the user can select directly the clients name and data of this client will automatically be filled in the form.

- Also in the "search" form we place this listbox so the user will search on the right name! and see all the data.

- The search will be centralized in an "Search" sheet where all data of this client will dis-plied.

- The user will select the data he needs to make a report and send this information to a special layout for printing.

I have attached the last state of the project.
Please give me comment if this is not what You want !!

PS:
Here is some advice on projects like this (it will shorten the development time and facilitate your work):

- Always make an Analise of the project.
Which means: consult the users on what they want: to do, to see, how work with, ... the data.
Because a programmer has his own ideas how a program can look like with fancy things but it is not always what the user wants.

- Study the ergonomic of the program: what must the user see at first, what is information on the screen (titles of textboxes not so big and data good visible)

- if possible, automate the work of the user like: dropdownlist (advantage= fast and correct data), get data from elsewhere, ...

- On the programming self:

Use clear explaining names (use a standard code) for different things like (I use this for fast and easy reading of the code):

Variables: clear description (All caption) = ROWIDX, USERNAME,...

Elements on the form: type (caption) description = CMDopen, TXTdate, ...

Name of function: verb_element underscore what= Open_File,...

Use for For...Next counter a description in stead of i, j, k like:
for ROWIDX=1 to 10
Because in more nested loops it's difficult to keep track of the different indexes used and which you must use in a code like cells(i,j) ?? cells(k,i) ?? better: cells(ROWIDX, COLIDX)

Add comment (not to much) to the code so You (or someone else) can reed it faster and understand the logic of it.
It will even help You if you have to change or adapt the code after a while.

Add indents in blocks of code like for...next, select case, if ...then, ...(you can detect faster if an "End..." is missing because VB gives sometimes an error like "with without END with" while there is a "For...next" without "Next" in it !!!)

I have not yet changed the names in attached project because I want first Your opinion on these suggestions !!!

[vbanewbie2]

that lloks cool and the advice is great, both things I would have not thought of although I been informed to indent my code by my teacher. I like the calendar touch and adding a client list. SO every time the user adds a client in the txtboxes if they click add clients it goes to the client list and can be recalled at any time. oool. All the other entries in when you hit enter though do not go anywhere. Did I do something wrong on my enter command button?

P.s.
I just hope and pray that someday I'm as good as you are at this. How long have you been doing VBA? I'm reading up using Microsoft Visual Basic.net Reloaded. It teaches the basics as well as Visual basic 6. Is there any other book out there that you can recommend me to study/read to become better accomplished at this.

[Guido Geurs]

Your "ENTER" code was going nowhere!

Set ws = Worksheets(Format(Date, "MMMYYYY"))
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

You must first define to which sheet and then to the first empty ROW.

like:

Define which sheet=

      Select Case MONTH
      Case 1: WSNAME = "JAN2010"
      Case 2: WSNAME = "FEB2010"
      ....
 

Activate it and go to the first empty row=

            WS.Activate
            WS.Range("A1").End(xlDown).Offset(1, 0).Activate

PS:

I have changed the code for that part because it gives an error if there is no data entered yet or there is only 1 row entered.
So it must start at A1 and be:

         If WS.Name = WSNAME Then
            WS.Activate
            WS.Range("A1").End(xlDown).Offset(1, 0).Activate
            With ActiveCell
               .Value = txtpo
               .Offset(0, 1) = txtname1

You see I also have still to learn a lot of things although I have started programming in 1975 (on a Texas TI94 with 12Kb RAM ) as a hobby but never have done it as a profession.
Although my jobs I have done were mostly with computers.

A good website with tutorials is:
http://www.theopensourcery.com/ostutor.htm#vba
with:
- Intro into Visual Basic
- The outline of the VB development Interface
- ...
http://www.vb6.us/guides/visual-basic-6-beginners-guide
with:
- Beginner Guide
- Controls Guide
- Database Guide
http://www.vbtutor.net/vbtutor.html
with:
- Introduction to Visual Basic 6
- Building VB Applications
- ...

Also You can Google for: "Ebook VB6 tutorial PDF"

The best thing to learn a programming language is always the practical way be solving real (industrial ) problems and also learning from other program codes.

But now back to our project=

- Must the name of the client be split into First and Last name ?
It gives us more programming (concatenate it for placing it in the listbox and splitting it again for searching it in the sheet) while maybe nobody is asking it for being splitted !

- Suggestions: always use the same name (or abbreviation of it) in the sheet, form, commands, ...( NOT OK: "First Name" = txtname1, ...)
Now we know its the same but after some months (and other projects) we (or someone else) will have to unravel the code before he understands the logic in the code.

- Also : use the names that the user of the program will us!

- Examine the data that must be entered: how long must the TEXTBOX be for a clear view of the data.

- Ask the user what the best layout is.(maybe for the ENTER form there is no need of a header with the name of the Firm, only on the reports and the printouts ??)

These are all points who must have an answer in the analysis.

[vbanewbie2]

Point 1). I'm not sure if the name has to be split, but what happens if we only have a last name of the client.? Thats just the example we were given.

great point on the suggestions!!! I know K.I.S.S. (keep it simple stupid) right! I get it.

on the textbox length, why does it have to be so long, cant we just word wrap it and scroll down?

There is no real need for the header on the form, It's just something I added. you can take that away.

Also thank you for the suggestions, on the education side. It really is appreciated. I want to get to know this stuff so bad.

[Guido Geurs]

Attached is an Excel with Search and Report.
Click in the menu on "Add Data" to open the form.
Select in the list of clients a name.
Click on [Search]
Click on [Close]
In the "search" sheet are all the found records.
Click in a cell of the line you want a report of.
Click in the menu on "Sheet Report" or "Form report" to view the data in the report.
The reports have only a few fields: it's up to you to define the final layout of it.

Ps: You can also make a report by selecting a cell in a row on a data sheet (like "JAN2010",...) and click in the menu on "Report..."


I hope this will help you on Your project.
If You still have some problems, please let me know.

[vbanewbie2]

I do have a couple questions if that is ok? Sorry I was on vacation but I'm back now and it looks pretty good. My questions are when the user accidentally types anything inside the COMBBclients it wants me to debug it (error), also the tab's seem to be all over the place. every time I try to correct them it goes right back to the way you have it is there something I'm missing? Last thing what are the macro commands to bring up the recoprt form and can I add on to it like what the job was and cost? thank you my friend.

[vbanewbie2]

Ok I was able to figure out how you added the items in the report form, pretty neat. I still have not been able to figure out the tab problem. also does the clients info have to be in the client spreedsheet in order to be printed on the report form. next week is turn in day. thanks again.

[Guido Geurs]

I have changed these things:(see attachment)

- COMBBclients :
The list is sorted and when You type a letter the list jumps to the first name that begins with the letter(s).

- The tabs: because the elements are in a FRAME and the elements are GROUPED (for easy layout modifications), the tabs act different !
They act in LAYERS: elements on the first level are the group "PO", "Date", frames and the command buttons.
In each frame the groups have there own tabindex and in these groups the elements have there tabindex.
If you jumps from "date" it goes to the next FRAME and in this frame to the first group and in this group to the first element.
It's possible to bypass the groups tabstops by setting the Tabstop to FALSE.
I have modiffied them but if the layput changes, they have to be addapted.
Or you can delete the GROUPS and FRAMES but the modiffication of the layout becommes more difficult.

- The macro's are in the Module =
Show_Add_Data_Form
Show_Report_Sheet
Show_Report_Form
You can call them with Macro - Run.

[Guido Geurs]

All data of the clients must be central (in the sheet) because it's used to search on.
It has also the advantage that there is less chance on mistakes in entering the data.

For the reports, the data of the clients is picked from the line you selected in a sheet:
For the Report FORM=

   With ActiveCell
      .End(xlToLeft).Activate
      Set DATA = Range("A" & .Row & ":AQ" & .Row)
   End With

For the Report SHEET=

   Worksheets("Report").Activate
   Cells(8, 1) = DATA(1, 2)   '§ first name
   Cells(8, 2) = DATA(1, 3)   '§ last name
   Cells(9, 1) = DATA(1, 6)   '§ street name
   Cells(9, 2) = DATA(1, 5)   '§ street no
   Cells(10, 1) = DATA(1, 8)  '§ city
   Cells(11, 1) = DATA(1, 7)  '§ tel no