How can I optimize my code? 4

[JensKKK]

How can I optimize my code?

As you can below I have very similar sub's and all I need to pack them into one sub is learn how

Sheets("menusheet").Select replace the "menusheet" expression with the sheet name. Any ideas welcome.

Thanks again

Sub Hide_T_Test()
'delete old sheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("T-Test").Delete
Application.DisplayAlerts = True
Sheets("menusheet").Select
Sheets("menusheet").Cells(12, 2) = "Show T-Test Data"
Sheets("menusheet").Cells(12, 3) = "show_T_Test"
Application.Run "createMenu"
Sheets("results").Select
Application.ScreenUpdating = True

End Sub

Sub Hide_Percent_Inhibition()
'delete old sheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("% Inhibition").Delete
Application.DisplayAlerts = True
Sheets("menusheet").Select
Sheets("menusheet").Cells(13, 2) = "Show % Inhibition Data"
Sheets("menusheet").Cells(13, 3) = "show_Percent_inhibition"
Application.Run "createMenu"
Sheets("results").Select
Application.ScreenUpdating = True

End Sub
Sub Hide_dRFU()
'delete old sheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("dRFU").Delete
Application.DisplayAlerts = True
Sheets("menusheet").Select
Sheets("menusheet").Cells(14, 2) = "Show dRFU Data"
Sheets("menusheet").Cells(14, 3) = "show_dRFU"
Application.Run "createMenu"
Sheets("results").Select
Application.ScreenUpdating = True

End Sub

 

[xlbo]

How are these subs called ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JensKKK]

Sub Hide_T_Test()

Sub Hide_Percent_Inhibition()

Sub Hide_dRFU()

 

[Fr33dan]

If you are calling the subs from other subs you could make them all into one that accepts a argument. Also the Sheets("menusheet").Select calls are unnecessary (also the Sheets("results").Select if your using that to return to the sheet you started from)

 

[JensKKK]

Fr33dan,

I think I got you on the wrong foot.

What I want to know is how I can select the following three sheets. At the moment I am using 3 different subs to do that job (as listed above). However I wonder if someone can give me a hint for the syntax of replacing

Sheets("T-Test").Select
Sheets("% Inhibition").Select
Sheets("dFRU").Select

with something like

Sheets(MySheetVariable).Select

 

[SkipVought]

Why are you SELECTING sheets?

faq707-4105

I recommend avoiding the use of the Select and Activate methods as much as possible.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[JensKKK]

Oh my mistake Skip

It should have been .delete instead of select.



Sheets("T-Test").delete
Sheets("% Inhibition").delete
Sheets("dFRU").delete

with something like

Sheets(MySheetVariable).delete

 

[SkipVought]

Looks good to me. Is there a problem?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Bong]

If you look at the 3 sub's, and cross your eyes just right, it seems there are 3 things (technical term) that are different. In fact they can all be built up from a single parameter (T-Test, % Inhibition, or dRFU) but let's even say all 3 are arguments:

Sub YourNameHere([red]a[/red] [green]b[/green] [blue]c[/blue])
'delete old sheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets([red]a[/red]).Delete
Application.DisplayAlerts = True
[s]Sheets("menusheet").Select[/s]
Sheets("menusheet").Cells(12, 2) = [green]b[/green]
Sheets("menusheet").Cells(12, 3) = [blue]c[/blue]
Application.Run "createMenu"
[s]Sheets("results").Select[/s]
Application.ScreenUpdating = True
End Sub

_________________
Bob Rashkin

 

[xlbo]

that is why I asked how you are calling the subs - if they are called from another sub, you can pass parameters to them - if they are called from individual command buttons then you would need a sub attached to each button that passes the paramters to the final sub..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Fr33dan]

Sorry I did miss understand what you wanted. If the sheets are next to each other in the workbook then you could reference them by index and use a loop through them.

Sub Hide_Sheets()
    'delete old sheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For i = 1 To 3
        sheetName = Sheets(1).Name
        Sheets(1).Delete
        Application.DisplayAlerts = True
        Sheets("menusheet").Cells(11 + i, 2) = "Show " & sheetName & " Data"
        Sheets("menusheet").Cells(11 + i, 3) = "show_" & sheetName
        Application.Run "createMenu"
        Sheets("results").Select
        Application.ScreenUpdating = True
    Next i
End Sub

 

[xlbo]

Bong has it but it still dpends on how the subs are called...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JensKKK]

Thanks to all of you.

I just tested the code and it seems to work.

Geoff, the sub is called from the menu bar and as you can see in the sub below it expects the page name (mysheet_name) and what I called the sheet_index.

I am also writing on the reverse action -> create a new page and fill it with data.

Thanks very much to all of you.

Regards

Jens


Sub Hide_sheet(mysheet_name As String, sheet_index As Integer)
'delete old sheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets(mysheet_name).Delete
Application.DisplayAlerts = True
Sheets("menusheet").Select
If sheet_index = 1 Then
Sheets("menusheet").Cells(10 + sheet_index, 2) = "Show Average Data"
Sheets("menusheet").Cells(10 + sheet_index, 3) = "Call_show_average"
End If
If sheet_index = 2 Then
Sheets("menusheet").Cells(10 + sheet_index, 2) = "Show T-Test Data"
Sheets("menusheet").Cells(10 + sheet_index, 3) = "Call_show_T_Test"
End If
If sheet_index = 3 Then
Sheets("menusheet").Cells(10 + sheet_index, 2) = "Show % Inhibition Data"
Sheets("menusheet").Cells(10 + sheet_index, 3) = "Call_show_percent_inhibition"
End If
If sheet_index = 4 Then
Sheets("menusheet").Cells(10 + sheet_index, 2) = "Show dRFU Data"
Sheets("menusheet").Cells(10 + sheet_index, 3) = "Call_show_dRFU"
End If
Application.Run "createMenu"
Sheets("results").Select
Application.ScreenUpdating = True

End Sub

 

[SkipVought]

Here's a general suggestion.

Insert a sheet for this data.

List all sheets in column A

dim ws as worksheet, r as long
r=2
With Sheets("ThisNewSheet")
  for each ws in worksheets
    .cells(r.1).value = ws.name
    r = r + 1
  next
end with

Mark the sheets to delete in column B

dim rng as range

With Sheets("ThisNewSheet")
  for each rng in .range(.[A2], .[A2].end(xldown))
    if rng.offset(0,1).value <> "" then _
       sheets(rng.value).delete    
  next
end with

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}