Trying to use "Call" to make a jump to 2nd subroutine but it always gives me errors

shayanhaque · Mar 13, 2019

I'm trying to use subroutine for Beforedoubleclick for all available sheets regardless of name or number and (Call Worksheet_BeforeDoubleClick) is giving me various kind of error and won't go to that subroutine. Please assist.

Here's my code

Code:

Sub Dosomething()
Dim pSheet As Worksheet


Application.ScreenUpdating = False
Set pSheet = ActiveSheet


For Each pSheet In Worksheets




Call Worksheet_BeforeDoubleClick


Next
Application.ScreenUpdating = True


End Sub


Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Step 1:  Declare Variables
    Dim strRange As String
'Step2:  Build the range string
    strRange = Target.Cells.Address & "," & _
               Target.Cells.EntireRow.Address
               Target.Cells.EntireRow.Interior.ColorIndex = 3
'Step 3: Pass the range string to a Range
    Range(strRange).Select
End Sub

NdNoviceHlp · Mar 13, 2019

U can't Call a worksheet event it just happens. Maybe add some info about your outcome and stop using sheet events... I hate them. Dave

shayanhaque · Mar 13, 2019

I’m fairly new to Vba. With double click i want to highlight specific row. In your opinion what is the best solution to implemet same double click subroutine for all possible sheets?

Akuini · Mar 13, 2019

With double click i want to highlight specific row.

Do you mean when you double click a cell you want the row of that cell to be highlighted ? or you want another specific row to be highlighted ?
This code will highlight the row where the cell is double clicked.
Put the code in ThisWorkbook code module.

Code:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

  Target.EntireRow.Interior.ColorIndex = 3
  End Sub

My Aswer Is This · Mar 13, 2019

I use sheet event scripts all the time.
What do you want to happen when you double click.

Are you wanting to double click on any cell in a row and set that rows interior color to 3

And you want this to happen on any sheet in your workbook.

My Aswer Is This · Mar 13, 2019

If the answer to my question is yes try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab

Select View Code from the pop-up context menu

In upper left corner of Window double click on ThisWorkbook

Paste this code in the VBA edit window

Code:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'Modified  3/13/2019  10:28:48 PM  EDT
Cancel = True
Dim ans As Long
ans = Target.Row
Rows(ans).Interior.ColorIndex = 3
End Sub

shayanhaque · Mar 13, 2019

Thank you for your help. it is working perfectly. Now my last question is if i want to make addin of this, will i have to place this code in module? if yes then how can i call this from module please?

My Aswer Is This · Mar 13, 2019

The code I provided is to be installed like I mentioned. This script does not go in a module.

If this works perfectly why do you want to run this from a module.

shayanhaque · Mar 13, 2019

I want to make a addin so that we just run throw addin regardless of work sheet and it will highlight rows. I don’t want to put it into this workbook manually.

Open any any file

Run addin

Highlight rows

My Aswer Is This · Mar 14, 2019

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

shayanhaque said:
I want to make a addin so that we just run throw addin regardless of work sheet and it will highlight rows. I don’t want to put it into this workbook manually.

Open any any file Run addinHighlight rows

Trying to use "Call" to make a jump to 2nd subroutine but it always gives me errors

shayanhaque

New Member

Excel Facts

NdNoviceHlp

Well-known Member

shayanhaque

New Member

Akuini

Well-known Member

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

shayanhaque

New Member

My Aswer Is This

Well-known Member

shayanhaque

New Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

Trying to use "Call" to make a jump to 2nd subroutine but it always gives me errors

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock