Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to run multiple procedure within Vba

  Asked By: Willie    Date: Mar 04    Category: MS Office    Views: 1624
  

I have three procedure that i need to run on each worksheet.I can get
two to work but when i add the third it tell me there is something
wrong with the original two.Can any one help me refine my code and
combine all this into one.

1st Procedure is to Hide certain rows within each sheet.
2nd Procedure is to change characters inputed to Caps.
3rd Procedure is for conditional formating of cells colour.

1st Procedure

Private Sub ToggleButton1_Click()

If ToggleButton1.Value = True Then
'This area contains the things you want to happen
'when the toggle button is not depressed
Rows(6).EntireRow.Hidden = True
Rows(8).EntireRow.Hidden = True
Rows(10).EntireRow.Hidden = True
Rows(12).EntireRow.Hidden = True
Rows(14).EntireRow.Hidden = True
Rows(16).EntireRow.Hidden = True
Rows(18).EntireRow.Hidden = True
Rows(20).EntireRow.Hidden = True
Rows(22).EntireRow.Hidden = True
Rows(24).EntireRow.Hidden = True
Rows(26).EntireRow.Hidden = True
Rows(28).EntireRow.Hidden = True
Rows(30).EntireRow.Hidden = True
Rows(32).EntireRow.Hidden = True
Rows(34).EntireRow.Hidden = True
Rows(36).EntireRow.Hidden = True
Else
'This area contains the things you want to happen
'when the toggle button is depressed
Rows(6).EntireRow.Hidden = False
Rows(8).EntireRow.Hidden = False
Rows(10).EntireRow.Hidden = False
Rows(12).EntireRow.Hidden = False
Rows(14).EntireRow.Hidden = False
Rows(16).EntireRow.Hidden = False
Rows(18).EntireRow.Hidden = False
Rows(20).EntireRow.Hidden = False
Rows(22).EntireRow.Hidden = False
Rows(24).EntireRow.Hidden = False
Rows(26).EntireRow.Hidden = False
Rows(28).EntireRow.Hidden = False
Rows(30).EntireRow.Hidden = False
Rows(32).EntireRow.Hidden = False
Rows(34).EntireRow.Hidden = False
Rows(36).EntireRow.Hidden = False

End If

End Sub

2nd Procedure

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Formatting all input as caps

Static booBusy As Boolean
If booBusy = True Then Exit Sub
booBusy = True
Dim rngCell As Range, rngIntersect As Range
Set rngIntersect = Application.Intersect(Target, Range("B1:AQ37"))
On Error Resume Next
For Each rngCell In rngIntersect
rngCell.Value = UCase(rngCell.Value)
Next rngCell
On Error GoTo 0
booBusy = False

End Sub

3rd Procedure

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

' Conditional Formatting for more than 3 conditions

Dim rng As Range


Set rng = Intersect(Target, Range
("I7:M37,Q7:T37,W7:AA37,AD7:AH37,AK7:AK37"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case ""
cl.Interior.ColorIndex = 0
Case "V"
cl.Interior.ColorIndex = 44
Case "M"
cl.Interior.ColorIndex = 43
Case "C"
cl.Interior.ColorIndex = 6
Case "T"
cl.Interior.ColorIndex = 32
Case "TB"
cl.Interior.ColorIndex = 33
Case "H"
cl.Interior.ColorIndex = 4
Case "HD"
cl.Interior.ColorIndex = 4
Case "S"
cl.Interior.ColorIndex = 3
Case "B"
cl.Interior.ColorIndex = 16
Case Else
Exit Sub
End Select
Next cl
End If

End Sub

Share: 

 

14 Answers Found

 
Answer #1    Answered By: Delbert Cooper     Answered On: Mar 04

Your second and third subroutines have the same name. You can't do that.
You will need to combine the code  from these two into one subroutine.

 
Answer #2    Answered By: Myron James     Answered On: Mar 04

Is there anyway of specifing the hide  cells routine in an easier
argument instead of each row being named?

 
Answer #3    Answered By: Vidisha Pathak     Answered On: Mar 04

Private Sub ToggleButton1_Click()

With ToggleButton1
Rows(6).Hidden = .Value
Rows(8).Hidden = .Value
Rows(10).Hidden = .Value
Rows(12).Hidden = .Value
Rows(14).Hidden = .Value
Rows(16).Hidden = .Value
Rows(18).Hidden = .Value
Rows(20).Hidden = .Value
Rows(22).Hidden = .Value
Rows(24).Hidden = .Value
Rows(26).Hidden = .Value
Rows(28).Hidden = .Value
Rows(30).Hidden = .Value
Rows(32).Hidden = .Value
Rows(34).Hidden = .Value
Rows(36).Hidden = .Value
End With
End Sub

2 nd Procedure

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Formatting all input  as caps
Dim rngCell As Range

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Range("B1:AQ37")) Is Nothing Then

For Each rngCell In Target
rngCell.Value = UCase(rngCell.Value)
Next rngCell

ElseIf Not Intersect(Intersect(Target, _
Range("I7:M37,Q7:T37,W7:AA37,AD7:AH37,AK7:AK37"))) Is Nothing
Then
For Each cl In Target
Select Case cl.Text
Case "": cl.Interior.ColorIndex = 0
Case "V": cl.Interior.ColorIndex = 44
Case "M": cl.Interior.ColorIndex = 43
Case "C": cl.Interior.ColorIndex = 6
Case "T": cl.Interior.ColorIndex = 32
Case "TB": cl.Interior.ColorIndex = 33
Case "H": cl.Interior.ColorIndex = 4
Case "HD": cl.Interior.ColorIndex = 4
Case "S": cl.Interior.ColorIndex = 3
Case "B": cl.Interior.ColorIndex = 16
End Select
Next cl
End If

ws_exit:
Application.EnableEvents = True
End Sub

 
Answer #4    Answered By: Barney Smith     Answered On: Mar 04

Could you maybe explain your version of the code  please?

1st procedure  works fine but i was wondering if the hidden rows  could
be held in one argument ie(2,4,6,8,10)ect

2nd Procedure doesn't work  so could you explain what you've changed and
why.

 
Answer #5    Answered By: Bu Nguyen     Answered On: Mar 04

Private Sub ToggleButton1_Click()

With ToggleButton1
Range("A6, A8, A10, A12, A14, A16, A18, A20, A22, A24, A26,
A28, A30, A32, A34, A36").EntireRow.Hidden = .Value
End With
End Sub


In what way doesn't the second work?

 
Answer #6    Answered By: Alonzo Roberts     Answered On: Mar 04

Is there a way of Protecting the rows  that i hide  and then unprotect
them when i unhide them?

When these rows are hidden  it's possible to delete the data in them if
i select the rows around them and press delete even if they are
hidden.The users of my spreadsheet might delete these by mistake if
they are hidden.

Any ideas how i can do this?

 
Answer #7    Answered By: Dion Jones     Answered On: Mar 04

To protect and hide  rows, you could try:

Cells.Locked = False
Rows("RowNum:RowNum").Locked = True
Rows("RowNum:RowNum").Hidden = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True

This would keep the row from being deleted, but the user would get the
"... is protected and is therfore read-only." Excel error  message when
the data is mistakenly deleted. I don't know how to avoid this error
message without what would probably be a great deal of work.

 
Answer #8    Answered By: Lurleen Fischer     Answered On: Mar 04

e.g.

for rownr = 6 to 36 step 2

Rows(rownr).EntireRow.Hidden = False

next rownr

 
Answer #9    Answered By: Helina Bonkob     Answered On: Mar 04

There is no automated link between protection and hiding.

I tend not to hide  rows in the main part of a worksheet. I put information
I don't want cluttering up the place onto a different worksheet, or way off
to the right or way down at the bottom.

There's nothing to stop you protecting the information you're going to hide,
though. You just need to do it separately.

 
Answer #10    Answered By: Cara Lewis     Answered On: Mar 04

The cells  i hide  have to be where they are as they represent the value
of the holiday taken. I hide these rows  with a toggle button  so that i
can have a better overwiew of staff who are on holiday.

If someone was to delete some info by highlighting more than one row
then the hidden  cell values get deleted as well.

So i need to protect these cells when they are hidden and unprotect
when they are not.

I can do one or the other but i can't do both procedures using the same
button.

If i send you the spreadsheet would that help  you see what i'm trying
to do?

 
Answer #11    Answered By: Luis Fischer     Answered On: Mar 04

Quick question regarding the "extra" information.
In Excel if the cell has data in it, it will try to print the cells.
How do you get around this? Do you set your page area? What about
changes? Could the information be put on an extra worksheet  and then
use links then protect the linked cells  and the worksheet with the
original data?
I find that my users forget to reset the print area  then wonder why all
their information is not there.

 
Answer #12    Answered By: Xavier Thompson     Answered On: Mar 04

Unless you are changing them on a regular basis, the cells  with the hours in
them don't need to be anywhere near the "H" cells or the formulas - they can
just as easily be on a different sheet.

In most instances, these hour values would be common either from day to day
or from week to week, and probably even from person to person. This would
mean that you don't need an hours value for every day anyway - you could
have all the formulas referring to a few common values.

However, I don't know your specific circumstances. If you want to keep this
information close to the other information, then that's fine, and you just
need to protect the cells at the same time as you hide  the rows.

 
Answer #13    Answered By: Damon Perez     Answered On: Mar 04


What i'd like to do is specify a range  of cells  to be protected when hidden  ie
I6:M6,P6:T6,W6:AA6,AD6:AH6
and the same cells to be unprotected when unhidden.All controlled by a toggle
switch.

I also need the same on alternate rows  ie I8,I10,I12 ect

Any guidance on this very much appreicated.

Here is the code  i'm using.



Private Sub ToggleButton1_Click()

With ToggleButton1

Range("A6,A8,A10,A12,A14,A16,A18,A20,A22,A24,A26,A28,A30,A32,A34,A36").EntireRow\
.Hidden = .Value

End With
End Sub


Private Sub Worksheet_Change(ByVal target  As Range)

' Character Input as Caps

Static booBusy As Boolean
If booBusy = True Then Exit Sub
booBusy = True
Dim rngCell As Range, rngIntersect As Range
Set rngIntersect = Application.Intersect(Target, Range("B1:AQ37"))
On Error Resume Next
For Each rngCell In rngIntersect
rngCell.Value = UCase(rngCell.Value)
Next rngCell
On Error GoTo 0
booBusy = False

'Conditional Formatting

Dim rng As Range

Set rng = Intersect(Target,
Range("I7:M37,Q7:T37,W7:AA37,AD7:AH37,AK7:AK37"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case ""
cl.Interior.ColorIndex = 0
Case "V"
cl.Interior.ColorIndex = 42
Case "M"
cl.Interior.ColorIndex = 45
Case "C"
cl.Interior.ColorIndex = 7
Case "T"
cl.Interior.ColorIndex = 14
Case "TB"
cl.Interior.ColorIndex = 5
Case "H"
cl.Interior.ColorIndex = 4
Case "HD"
cl.Interior.ColorIndex = 4
Case "S"
cl.Interior.ColorIndex = 3
Case "B"
cl.Interior.ColorIndex = 16
Case "MD"
cl.Interior.ColorIndex = 43
Case Else
Exit Sub
End Select
Next cl
End If

End Sub

 
Answer #14    Answered By: Betty Fischer     Answered On: Mar 04

You are correct. If the extra information is on the same sheet, you will
need a print area.

If this is a problem, then an extra worksheet  is often a good way to go.

However, a workbook with this level of complexity is probably best not given
to users in a mode where they can add  rows or columns. They'll break it in
more ways than just not printing the extra stuff.

 
Didn't find what you were looking for? Find more on How to run multiple procedure within Vba Or get search suggestion and latest updates.




Tagged: