forms

[David_UK]

I have a shared workbook on a shared drive it consists of four worksheets.

They are designed purely for recording daly cash figures i.e.

Account name Acct No. Chq # Amount Invoice numbers

I wish to create possibly using a form or VBA, a request box to appear when the sheet is opened asking for the account number and then search all sheets for the number and then either going to that cell or displaying account not found?

Any help would be great, thanks.

David.

 

[LTunnicliffe]

Stick this code in the worksheet section:

Private Sub Worksheet_Activate()

  Tries = 0
NumAgain:
  Tries = Tries + 1
  AcctNum = InputBox("Please enter the account number:", "Account Number", "XXXXXXXXX")
  If AcctNum = "" And Tries <> 3 Then
    GoTo NumAgain
  Else: If Tries = 3 Then Exit Sub
  End If
  
  
  Range("A1:E" & Range("A65536").End(xlUp).Row).AutoFilter Field:=2, Criteria1:=AcctNum

End Sub

Hope this works for you!

 

[LTunnicliffe]

I realized that won't work for what you are asking so here's this. You can then call this macro in the Worksheet_Activate on the any of the worksheets.

Sub FilterAllSheets()
Dim wkSht As Worksheet
   
  Tries = 0
NumAgain:
  Tries = Tries + 1
  AcctNum = InputBox("Please enter the account number:", "Account Number", "XXXXXXXXX")
  If AcctNum = "" And Tries <> 3 Then
    GoTo NumAgain
  Else: If Tries = 3 Then Exit Sub
  End If
  
  For Each wkSht In Sheets
    
    If wkSht.AutoFilterMode = False Then wkSht.Range("A1:E" & Range("A65536").End(xlUp).Row).AutoFilter
    wkSht.Range("A1:E" & Range("A65536").End(xlUp).Row).AutoFilter Field:=2, Criteria1:=AcctNum
    
  Next wkSht
End Sub

Hope this works better!

 

[David_UK]

Thanks for that my friend,

YOu will have to forgive me I am new to VBA, do I open up my workbook and then go to VBA and type this in then save it?

Sorry I should have said before!

David.

 

[LTunnicliffe]

No problem!!! If this gets too detailed forgive me.

Open your workbook, press Alt+F11 (to get to the VBE), then double click the worksheet that you want to activate the macro. In the windown put this code:

Private Sub Worksheet_Activate()
  Call FilterAllSheets
End Sub

Then in the project exlorer (on the left) add a module and put the code from my second post in it.

 

[David_UK]

It now come up with a compile error

Else without if

it's where Else: If Tries = 3 Then Exit Sub
End If

also Next wkSht

is a compile error

Do you understand?

 

[LTunnicliffe]

Strange it compiled and worked on mine. What version of Excel are you running?

In the mean time, remove the colon and put a carriage return there.

LT

 

[David_UK]

Excel 2000

Carriage Return? Sorry mate?

I think I have done what you said, I double clicked sheet one and wrote the Call FilterAllSheets

Then I created a new module and typed your program to the letter, then I saved it and ran the program while still in VBE,

Then I closed it and ran the macro both occasions came up with the error.

Also When I close the worksheet and the multiple users open it and enable the macro will the input box show automatically i.e. they have to enter the account number to procede?

Sorry, I am trying to learn VBA, but it's tricky!

Thanks,

David. :wink:

 

[LTunnicliffe]

Change it to this:

  If AcctNum = "" And Tries <> 3 Then 
    GoTo NumAgain 
  Else
    If Tries = 3 Then Exit Sub 
  End If

You should just be able to copy the code from here and paste over the other code.

When I close the worksheet and the multiple users open it and enable the macro will the input box show automatically i.e. they have to enter the account number to procede?

No, if you want it to then insert this code in the ThisWorkbook section:

Private Sub Workbook_Open()
  Call FilterAllSheets
End Sub

 

[David_UK]

Thanks mate I will try it, I may as well paste the whole lot in that case!

I will let you know how I get on! Also are there any websites that you know of that I could download tips or any information on VBA.

Thanks,

David.