#1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 62
Default Call

Hi There,

Macro should recornize my "TargettedActiveSheet" as the active sheet openned
in Excel. The active sheet's name should be returned & set as
"TargettedActiveSheet" so that my macro call use this returned name to call
the correct procedure that is intended to work on the active sheet.

So, if the active sheet name is Sheet1, then "TargettedActiveSheet" should
return Sheet1 as the reference for macro. If Sheet99 is the active sheet,
then "TargettedActiveSheet" should return Sheet99 so that Macro can recognize
the right name in order to invoke CALL the the sub procedure named "Sheet99"
so as to execute code & work out the macro I intended for Sheet99 or Sheet1
or any other active sheet.

How can I use make Macro do this? I tried the below but to no avail.

Sub Identify_And_Optimize_Target_File()
Dim TargettedActiveSheet As String
TargettedActiveSheet = ActiveWorkbook.ActiveSheet.Name
Call TargettedActiveSheet
End Sub


Private Sub Sheet1()
'Do as I intend for sheet1
End Sub


Private Sub Sheet99()
'Do as I intend for sheet99
End Sub

Thanks a lot

--
Edmund
(Using Excel XP)
  #2   Report Post  
Posted to microsoft.public.excel.programming
GS GS is offline
external usenet poster
 
Posts: 364
Default Call

Hi Edmund,

What you're doing is not proper syntax. For example, if the active sheet's
name is "Sheet1" then using the string variable like you are means you're
actually doing this:

Call "Sheet1"

when what you want is:

Call Sheet1

You need to implement a select case structure so you can redirect based on
the sheetname. ..Just one way to go!

HTH
Regards,
GS
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,588
Default Call

Try

Application.Run ActiveSheet.Name

Tim


"Edmund" wrote in message
...
Hi There,

Macro should recornize my "TargettedActiveSheet" as the active sheet
openned
in Excel. The active sheet's name should be returned & set as
"TargettedActiveSheet" so that my macro call use this returned name to
call
the correct procedure that is intended to work on the active sheet.

So, if the active sheet name is Sheet1, then "TargettedActiveSheet" should
return Sheet1 as the reference for macro. If Sheet99 is the active sheet,
then "TargettedActiveSheet" should return Sheet99 so that Macro can
recognize
the right name in order to invoke CALL the the sub procedure named
"Sheet99"
so as to execute code & work out the macro I intended for Sheet99 or
Sheet1
or any other active sheet.

How can I use make Macro do this? I tried the below but to no avail.

Sub Identify_And_Optimize_Target_File()
Dim TargettedActiveSheet As String
TargettedActiveSheet = ActiveWorkbook.ActiveSheet.Name
Call TargettedActiveSheet
End Sub


Private Sub Sheet1()
'Do as I intend for sheet1
End Sub


Private Sub Sheet99()
'Do as I intend for sheet99
End Sub

Thanks a lot

--
Edmund
(Using Excel XP)



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default Call

can you even use a reserved name like sheet1 to name a macro?

--


Gary


"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
Try

Application.Run ActiveSheet.Name

Tim


"Edmund" wrote in message
...
Hi There,

Macro should recornize my "TargettedActiveSheet" as the active sheet openned
in Excel. The active sheet's name should be returned & set as
"TargettedActiveSheet" so that my macro call use this returned name to call
the correct procedure that is intended to work on the active sheet.

So, if the active sheet name is Sheet1, then "TargettedActiveSheet" should
return Sheet1 as the reference for macro. If Sheet99 is the active sheet,
then "TargettedActiveSheet" should return Sheet99 so that Macro can recognize
the right name in order to invoke CALL the the sub procedure named "Sheet99"
so as to execute code & work out the macro I intended for Sheet99 or Sheet1
or any other active sheet.

How can I use make Macro do this? I tried the below but to no avail.

Sub Identify_And_Optimize_Target_File()
Dim TargettedActiveSheet As String
TargettedActiveSheet = ActiveWorkbook.ActiveSheet.Name
Call TargettedActiveSheet
End Sub


Private Sub Sheet1()
'Do as I intend for sheet1
End Sub


Private Sub Sheet99()
'Do as I intend for sheet99
End Sub

Thanks a lot

--
Edmund
(Using Excel XP)





  #5   Report Post  
Posted to microsoft.public.excel.programming
GS GS is offline
external usenet poster
 
Posts: 364
Default Call

Hi Gary,

Yes, you can. Try this and see what happens.

Sub Sheet1()
Debug.Print "this works"
End Sub

Sub test()
Call Sheet1
End Sub

Regards,
Garry
"Gary Keramidas" wrote:

can you even use a reserved name like sheet1 to name a macro?

--


Gary


"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
Try

Application.Run ActiveSheet.Name

Tim


"Edmund" wrote in message
...
Hi There,

Macro should recornize my "TargettedActiveSheet" as the active sheet openned
in Excel. The active sheet's name should be returned & set as
"TargettedActiveSheet" so that my macro call use this returned name to call
the correct procedure that is intended to work on the active sheet.

So, if the active sheet name is Sheet1, then "TargettedActiveSheet" should
return Sheet1 as the reference for macro. If Sheet99 is the active sheet,
then "TargettedActiveSheet" should return Sheet99 so that Macro can recognize
the right name in order to invoke CALL the the sub procedure named "Sheet99"
so as to execute code & work out the macro I intended for Sheet99 or Sheet1
or any other active sheet.

How can I use make Macro do this? I tried the below but to no avail.

Sub Identify_And_Optimize_Target_File()
Dim TargettedActiveSheet As String
TargettedActiveSheet = ActiveWorkbook.ActiveSheet.Name
Call TargettedActiveSheet
End Sub


Private Sub Sheet1()
'Do as I intend for sheet1
End Sub


Private Sub Sheet99()
'Do as I intend for sheet99
End Sub

Thanks a lot

--
Edmund
(Using Excel XP)








  #6   Report Post  
Posted to microsoft.public.excel.programming
GS GS is offline
external usenet poster
 
Posts: 364
Default Call

Hi Tim,

< Application.Run ActiveSheet.Name
The data type of a sheetname is String, therefore yields the "The macro
'Sheet1' cannot be found." error.

Call ActiveSheet.Name
returns the "Object doesn't support this property or method." error.


This:
Dim sName As Variant
sName = ActiveSheet.Name
Call sName
returns the "Compile Error: Expected Sub, Function, or Property" message.

Regards,
Garry

"Tim Williams" wrote:

Try

Application.Run ActiveSheet.Name

Tim


"Edmund" wrote in message
...
Hi There,

Macro should recornize my "TargettedActiveSheet" as the active sheet
openned
in Excel. The active sheet's name should be returned & set as
"TargettedActiveSheet" so that my macro call use this returned name to
call
the correct procedure that is intended to work on the active sheet.

So, if the active sheet name is Sheet1, then "TargettedActiveSheet" should
return Sheet1 as the reference for macro. If Sheet99 is the active sheet,
then "TargettedActiveSheet" should return Sheet99 so that Macro can
recognize
the right name in order to invoke CALL the the sub procedure named
"Sheet99"
so as to execute code & work out the macro I intended for Sheet99 or
Sheet1
or any other active sheet.

How can I use make Macro do this? I tried the below but to no avail.

Sub Identify_And_Optimize_Target_File()
Dim TargettedActiveSheet As String
TargettedActiveSheet = ActiveWorkbook.ActiveSheet.Name
Call TargettedActiveSheet
End Sub


Private Sub Sheet1()
'Do as I intend for sheet1
End Sub


Private Sub Sheet99()
'Do as I intend for sheet99
End Sub

Thanks a lot

--
Edmund
(Using Excel XP)




  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 62
Default Call

Hello

Let me tell u my full scenario. Perhaps u can suggest how can I achieve it.

At work, I download query reports (database) into Excel file. Everyday, I hv
to download 5 reports. (Note : I store all the 5 downloaded reports in 5
separate Excel sheets but they are all under 1 single Excel file).

What made life difficult was that the reports are haphazardly formatted &
delimited(with lots of spaces & weird random formatting). Instead of spending
time daily in adjusting manually for each sheet, I attempted to compile 5
different macro procedures to rectify each sheet's data.

Let's say:
Sheet1 hold data for Purchase Price
Sheet2 holds Vendor Information
Sheet3 holds Contract Information
Sheet4 holds Overdue P/O
Sheet5 holds Outstanding Production Backlog

My 5 macros are tailored to cater for rectification for each sheet. But to
select & press the button in ToolsMacro to execute the codes daily, risks
accidental execution. To make life easier, I'm tyring to derive a macro that
will "take the active sheet's name so as to use that name as the guide for it
to grab the correct procedure to execute". That's why in VBE , procedure
inside "Sub Sheet1()" is intended to rectify Sheet1's inconsistency".

In short, I just need a procedure that will take the name of the active
sheet, & use that name to trigger the right macro that is intended for that
specific sheet. When this is successful, I can rest my worries as I only need
to run 1 single macro, where this macro will trigger the execution of the
right procedure of the 5 procedures in hand.

I'm a VBA rookie. Very very raw to VBA.

--
Edmund
(Using Excel XP)

  #8   Report Post  
Posted to microsoft.public.excel.programming
GS GS is offline
external usenet poster
 
Posts: 364
Default Call

Hi Edmund,

It's a good thing you currently only have 5 sheets to "worry" about. Adding
more sheets will be a simple matter of writing each one's procedure, and
adding a call statement to it. That said, constructing the calling procedure
is fairly simple.

How I suggest you handle it is by looping through each sheet in the workbook
and redirecting the code to the procedure associated with that sheet. You can
use its name to do this just like you want, using a Select Case structure. I
strongly suggest you use other names for the sheets. Something that
associates their nature would be more appropriate. For example:

PurchasePrice
VendorInfo
ContractInfo
OverduePO
ProductionBacklog

Here's how:

Sub FormatSheets()
' This loops through the sheets in the ActiveWorkbook,
' to call a procedure according to each sheet's name.

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
Select Case wks.Name
Case Is = "PurchasePrice": Call FormatPurchasePrice
Case Is = "VendorInfo": Call FormatVendorInfo
Case Is = "ContractInfo": Call FormatContractInfo
Case Is = "OverduePO": Call FormatOverduePO
Case Is = "ProductionBacklog": Call FormatProductionBacklog
'Insert new sheets here as required
End Select
Next wks

End Sub

You can put this in a standard module along with the called procedures for
each sheet.

HTH
Regards,
GS
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Call Center Management: How to calculate 'cost per call' Denniso6 Excel Discussion (Misc queries) 2 June 25th 06 05:01 PM
Don't know what to call what I need? chanwando Excel Worksheet Functions 5 September 9th 05 10:01 PM
Run or Call MBlake[_2_] Excel Programming 7 August 27th 05 09:29 PM
call sub mike allen[_2_] Excel Programming 3 October 15th 04 04:54 PM
How do you call one Sub from another Sub ? lothario[_30_] Excel Programming 2 October 17th 03 01:47 PM


All times are GMT +1. The time now is 09:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"