Using IF statement and managing toolbars

[Angie M.]

Hello, I'm using Excel 03. I have two questions. First, I have 3 different
users accessing the same Excel file (not at the same time), but before each
of them can run the macros I have to rebuild the toolbar at each computer.
How can I make the toolbar live with the spreadsheet? I've tried Customize,
Toolbars and Attach, but this doesn't seem to work well. I tried Help also.
Is there a trick to this?

My second question is, I want to add an IF statement to the following macro,
so that if the BATCH NUMBER entered by the user is not located on the MAIN
sheet, they are greeted with a msgbox and the macro is exited. The code is
as follows:

Sub ReportBatch()

Dim Message2, Title2
Dim MyValue2 As String
Message2 = "Enter the Batch Number for Report"
Title2 = "Batch Number for Report"
MyValue2 = InputBox(Message2, Title2)

Dim ValA As Variant
[ValA] = MyValue2

Worksheets("Batch Report").Range("A2:G27").ClearContents

Worksheets("Main").Activate
Application.Goto reference:="R2C1"
Selection.AutoFilter
Selection.AutoFilter field:=2, Criteria1:="" & [ValA] & ""

IF THE BATCH # DOESN'T EXIST THEN MSGBOX ("WRONG NUMBER!") AND EXIT SUB
ELSE

Range("A2:G15270").Select
Selection.Copy
Sheets("Batch Report").Activate
Application.Goto reference:="R2C1"
ActiveSheet.Paste
Application.Goto reference:="R2C1"

Worksheets("Main").AutoFilterMode = False
Application.Goto reference:="R2C1"

End Sub

Thank you for any help!

 

[AA2e72E]

Try this approach:

on 1.

'restore the toolbars to default .. here
Select case application.username
case user1
' build here
case user2
'build here
case user3
'build here
case else
' default action ... may be a message
end select

on 2.
do a Lookup (VLookup or Hlookup depending on how the batch numbers are
stored) in a cell (somewhere): if it is empty or is an error, show message.

 

[Angie M.]

Thanks for your help, being a beginner with Excel VBA I tried to work through
Help's explanation of VLookup and tried to apply it to my macro as you
suggested, but I just messed it up. Is there an easier way? Thanks

Try this approach:

on 1.

'restore the toolbars to default .. here
Select case application.username
case user1
' build here
case user2
'build here
case user3
'build here
case else
' default action ... may be a message
end select

on 2.
do a Lookup (VLookup or Hlookup depending on how the batch numbers are
stored) in a cell (somewhere): if it is empty or is an error, show message.

Hello, I'm using Excel 03. I have two questions. First, I have 3 different
users accessing the same Excel file (not at the same time), but before each
of them can run the macros I have to rebuild the toolbar at each computer.
How can I make the toolbar live with the spreadsheet? I've tried Customize,
Toolbars and Attach, but this doesn't seem to work well. I tried Help also.
Is there a trick to this?

My second question is, I want to add an IF statement to the following macro,
so that if the BATCH NUMBER entered by the user is not located on the MAIN
sheet, they are greeted with a msgbox and the macro is exited. The code is
as follows:

Sub ReportBatch()

Dim Message2, Title2
Dim MyValue2 As String
Message2 = "Enter the Batch Number for Report"
Title2 = "Batch Number for Report"
MyValue2 = InputBox(Message2, Title2)

Dim ValA As Variant
[ValA] = MyValue2

Worksheets("Batch Report").Range("A2:G27").ClearContents

Worksheets("Main").Activate
Application.Goto reference:="R2C1"
Selection.AutoFilter
Selection.AutoFilter field:=2, Criteria1:="" & [ValA] & ""

IF THE BATCH # DOESN'T EXIST THEN MSGBOX ("WRONG NUMBER!") AND EXIT SUB
ELSE

Range("A2:G15270").Select
Selection.Copy
Sheets("Batch Report").Activate
Application.Goto reference:="R2C1"
ActiveSheet.Paste
Application.Goto reference:="R2C1"

Worksheets("Main").AutoFilterMode = False
Application.Goto reference:="R2C1"

End Sub

Thank you for any help!