PDA

View Full Version : About a Telephone Numbers Catalogue



Erdin? E. Ka
09-22-2006, 01:55 PM
Hi everyone!:hi:

I want a make a sipmle "Telephone Numbers Catalogue" in Excel 2003.

Are there anybody already have a similar sample and can share with me ?

Thanks a lot. :bow:

ndendrinos
09-22-2006, 04:00 PM
Hello Erdin? E. Kara?am
Maybe I can return the favour ... can you describe what you are looking for in more details ?
Regards,
NHick

ndendrinos
09-22-2006, 04:20 PM
Three examples the first one not so simple and you need to add the rest of the option buttons on the form ... maybe someone in this forum can help with this one and get it to work without the hassle of these option buttons.
The second and third example are very simple
Hope one can suit your needs.
Here is the first

ndendrinos
09-22-2006, 04:22 PM
The other two
Regards,
Nick

Erdin? E. Ka
09-22-2006, 11:58 PM
Hi Nick,
Thank you very much for your goodwill and sharing. I am so happy to this.
esspecially "address.xls" sample is great.!

Thank you!

Thank you!

Thank you!:friends: :bow:

Erdin? E. Ka
09-23-2006, 12:15 AM
Three examples the first one not so simple and you need to add the rest of the option buttons on the form ... maybe someone in this forum can help with this one and get it to work without the hassle of these option buttons.
The second and third example are very simple
Hope one can suit your needs.
Here is the first

I think that there is no need to using Option Button for "Rolodex.xls" sample.

Maybe we can use the codes below instead of OptionButton.
(Adding Into CommadnButton1_Click Event)


'
WhichSheet = Mid(TextBox1, 1, 1)
Select Case UCase(WhichSheet)
Case A
Sheets("A").Select
Case B
Sheets("B").Select
'
'
'
Case Z
Sheets("Z").Select

Case 1 Or 2 Or 3 Or 4 Or 5 Or 6 Or 7 Or 8 Or 9 Or 0
Sheets("123").Select
End Select
'

ndendrinos
09-23-2006, 06:51 AM
Still working to get rid of the option buttons.
ThanksErdin?

Erdin? E. Ka
09-23-2006, 08:11 AM
Take it easy Nick. :yes

benny
09-23-2006, 08:21 AM
May be this helps for "Rolodex.xls"


Option Explicit
Private Sub CommandButton1_Click()
Dim WitchSheet As String
Dim response As String
Dim LastRow As Object
WitchSheet = Left(TextBox1, 1)
Sheets(WitchSheet).Select
Set LastRow = ActiveSheet.Range("D65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
MsgBox "Data Recorded"
response = MsgBox("Do you want to enter another record?", _
vbYesNo)
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox1.SetFocus
Else
Sheets("INDEX").Select
Range("E1").Select
Unload Me
End If
End Sub

ndendrinos
09-23-2006, 01:26 PM
benny good work and thanks a ton ... Rolodex is now all done.
Regards,
Nick

mdmackillop
09-23-2006, 01:50 PM
Hi Benny,
Welcome to VBAX
A couple of minor points. They don't affect the functioning here but for future use:
The msgbox function returns an Integer. Response should be Integer or Long.
LastRow should be Range, rather than Object.
Regards
MD

Erdin? E. Ka
09-23-2006, 04:40 PM
I see Benny's code. It's ok but, the message above that i sent was wrong and which has something missing. For instead of that codes and for an alternative i am sending a new and coplete one (" For: Private Sub CommandButton1_Click event of Rolodex.xls ") below;

Private Sub CommandButton1_Click()
Dim LastRow As Object
Dim WhichSheet As String

WhichSheet = Mid(TextBox1, 1, 1)

Select Case UCase(WhichSheet)

Case "A"
Sheets("A").Select
Case "B"
Sheets("B").Select
Case "C"
Sheets("C").Select
Case "D"
Sheets("D").Select
Case "E"
Sheets("E").Select
Case "F"
Sheets("F").Select
Case "G"
Sheets("G").Select
Case "H"
Sheets("H").Select
Case "I"
Sheets("I").Select
Case "J"
Sheets("J").Select
Case "K"
Sheets("K").Select
Case "L"
Sheets("L").Select
Case "M"
Sheets("M").Select
Case "N"
Sheets("N").Select
Case "O"
Sheets("O").Select
Case "P"
Sheets("P").Select
Case "R"
Sheets("R").Select
Case "S"
Sheets("S").Select
Case "T"
Sheets("T").Select
Case "U"
Sheets("U").Select
Case "V"
Sheets("V").Select
Case "Y"
Sheets("Y").Select
Case "Z"
Sheets("Z").Select
Case "X"
Sheets("X").Select
Case "Q"
Sheets("Q").Select
Case "W"
Sheets("W").Select
Case 1 Or 2 Or 3 Or 4 Or 5 Or 6 Or 7 Or 8 Or 9 Or 0
Sheets("123").Select

End Select

Set LastRow = ActiveSheet.Range("D65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
MsgBox "Data Recorded"
response = MsgBox("Do you want to enter another record?", _
vbYesNo)
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox1.SetFocus
Else
Sheets("INDEX").Select
Range("E1").Select
Unload Me
End If
End Sub

Greetings...:hi:


And adding a deep note:
I try Benny's code now, while writing a Company name which is beginning with a Numeric character, then i am getting "Subscpirt Out Of Range" error and yellow debug row is on:


Sheets(WitchSheet).Select


row of the codes.. :(

mdmackillop
09-23-2006, 04:48 PM
You can reduce your select statement a bit

WhichSheet = Mid(TextBox1, 1, 1)
Select Case UCase(WhichSheet)
Case "A" To "Z"
Sheets(WhichSheet).Select
Case 0 To 9
Sheets("123").Select
End Select

Erdin? E. Ka
09-23-2006, 04:58 PM
You can reduce your select statement a bit

WhichSheet = Mid(TextBox1, 1, 1)
Select Case UCase(WhichSheet)
Case "A" To "Z"
Sheets(WhichSheet).Select
Case 0 To 9
Sheets("123").Select
End Select


Hi dear mdmackillop, :clap:

This is not a bit reduse! :whistle:

Thank you for great support:)

I love this Select-Case way. :thumb

ndendrinos
09-24-2006, 06:06 AM
Here is a lighter version of "Rolodex" and all edits to the code done.
There is still a problem and that is with "response"
Tried to fix it along Malcom's observation to Benny but could'nt
So to get it going I took "response" out of the code.
Any ideas?

Error =Compile Error Variable no Defined
Highlited=response

benny
09-24-2006, 07:54 AM
What you think of this one.

ndendrinos
09-24-2006, 11:09 AM
Benny great ... how much do you want for it? :beerchug:
Now all it needs is a search button on the form .... we'll see
Thanks for your help
Regards,
Nick

benny
09-24-2006, 12:09 PM
Nick,
you'r welcome. Glad i could help.
I added a search-combo and made the UF modeless.
So you can edit the sheets.
Have fun with it.

mdmackillop
09-24-2006, 12:14 PM
Hi Benny,
You're doing a great job here. The one thing I would add to complete the project is a Sort routine to the sheet when a new record is added.
Regards
MD

benny
09-24-2006, 12:51 PM
Thank you fir the compliment, MD.

Here the next version.

ndendrinos
09-24-2006, 12:53 PM
I agree with Malcom and thank you again Benny, BUT (there's always one)
I was thinking of introducing the concept in this example (provided courtesy of Malcom whereas the combobox would be a text box instead in which the user would type and search.

Two things need to be done (In my humble opinion that is):
1- the adaptation of the concept to the Form (and the result of the search would appear in the respective text boxes of the form)
2- the search would be in the sheet starting with the first letter of the typed word () or sheet 123 in the case of a numerical search
(in this example all the data is in one sheet)

mdmackillop
09-24-2006, 12:59 PM
Not sure I agree with the your search scenario. It seems to cut across the purpose of this form of presentation. If the form is to provide the search results then a simple data storage suffices, no need for the complication of the indexed spreadsheets.

ndendrinos
09-24-2006, 01:02 PM
I defer to your expert opinion and will use it just the way it is then
Thank You all for your contributions
Nick

Petrogeo
12-15-2006, 01:08 PM
About a telephone catalogue, and if I would like to replace or change the telephone number of a existing person????
Does anybody have a solution??
regards
Petrogeo

ndendrinos
12-17-2006, 01:00 PM
hello Petrogeo,
Doload the two samples found at the start of this posting under:

"The other two
Regards,
Nick"

Maybe you can adapt the sample named address to your needs?