Xl Command Button Help reqd.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Dara

Thread Starter
Joined
May 23, 2004
Messages
55
Hi,


I have a spreadsheet with three worksheets in it.

The first sheet holds information on shops, the second sheet information on hotels, and the third sheet information on retaraunts

Each retailer, be it a shop/hotel/r-raunt is denoted by a code i.e D306 , D308 etc.

I would like to add a worksheet to the spreadsheet, and put in a command button, so that when i click the button, it will search for a retailer in the three sheets, by searching for the Dxxx.


So if i press the command button a Msgbox will open asking me to enter the Dnumber i require. I type in the D number and the macro will find the D number among the thousands in the three sheets.....





Can anyone please help me with the VBA coding ?


Many thanks.
 
Joined
Aug 30, 2003
Messages
2,702
Hi Dara.

What version of Excel?

(You can get some code for this, but) have you considered combining the three sheets into one? With a separate column "Type" (Shop/Hotel/Restaurant), that you could AutoFilter on, to show just one (or more) types?
 
Joined
Aug 30, 2003
Messages
2,702
If you must use code, try this (which I cribbed, since I only do kludge code). Assumes the data sheets are Sheet1 - 3 and runs from Sheet4.

Sub Find_What()
Dim what As String
Dim cell As Range
Dim ws As Worksheet

what = InputBox("Enter D number.")

For Each ws In Worksheets

Set cell = ws.Range("a:a").Cells.Find(what) 'D numbers are in columns A

If Not cell Is Nothing Then
Range("Sheet4!A1") = cell.Offset(0, 1)
End If

Next

End Sub
 

Dara

Thread Starter
Joined
May 23, 2004
Messages
55
hi, thanks for that, it is in excel 97

i cant combine th three sheets into one unfortunately,


Tahsnk for your help, i am away to try it now !
 

Dara

Thread Starter
Joined
May 23, 2004
Messages
55
ok, sorry about this, but I am going to use the first sheet for the Command Button, and there are actually four subsequent sheets, not three.


So on the first sheet, i insert the command button, and right click view code etc...


and it starts with

Private Sub CommandButton1_Click()



do i just paste your code underneath this ?


sorry if i sound dumb !
 
Joined
Aug 30, 2003
Messages
2,702
>> do i just paste your code underneath this ?

Yes, but not the first & last lines (Sub/End Sub).

Personally I'd use a button from the Forms toolbar to avoid "Design Mode" issues. But then I'm retro.
 

Dara

Thread Starter
Joined
May 23, 2004
Messages
55
apparently i dont have the license to use the buttons off the forms toolbar..

having trouble getting this to work :(
 
Joined
Aug 30, 2003
Messages
2,702
Dara said:
apparently i dont have the license to use the buttons off the forms toolbar..

having trouble getting this to work :(
Now you have lost me. :confused:

Try the attached. The code is in the module for Sheet1. Access this module by r-clicking the sheet tab and selecting "View Code".

The code fires when a change is made to Sheet1!A1. Valid inputs for Sheet1!A1 are D1 - D16.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top