About a Telephone Numbers Catalogue

[Erdin? E. Ka]

Hi everyone!

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.

[ndendrinos]

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]

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]

The other two
Regards,
Nick

[Erdin? E. Ka]

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!

[Erdin? E. Ka]

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)

[VBA]
'
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
'
[/VBA]

[ndendrinos]

Still working to get rid of the option buttons.
ThanksErdin?

[Erdin? E. Ka]

Take it easy Nick.

[benny]

May be this helps for "Rolodex.xls"

[VBA]
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
[/VBA]

[ndendrinos]

benny good work and thanks a ton ... Rolodex is now all done.
Regards,
Nick

[mdmackillop]

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]

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;

[vba]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[/vba]

Greetings...


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:

[vba]
Sheets(WitchSheet).Select
[/vba]

row of the codes..

[mdmackillop]

You can reduce your select statement a bit
[vba]
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
[/vba]

[Erdin? E. Ka]

You can reduce your select statement a bit
[vba]
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
[/vba]

Hi dear mdmackillop,

This is not a bit reduse!

Thank you for great support

I love this Select-Case way.

[ndendrinos]

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]

What you think of this one.

[ndendrinos]

Benny great ... how much do you want for it?
Now all it needs is a search button on the form .... we'll see
Thanks for your help
Regards,
Nick

[benny]

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]

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]

Thank you fir the compliment, MD.

Here the next version.