Application.DoubleClick question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian - 29 Aug 2007 09:44 GMT

Hello all,

I have a sheet containing a matrix of Cross references. Using the case
statement, I would like to be able to loop through each cell in the range,
double click to go to the sheet containing the data, select the row, then
paste into a report sheet. Guess what -I can't get it to work. Any
suggestions please.

This is one case statement from the code:

Private Sub ComboBox1_click()
ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1
Dim refrange As Range
Dim c As Range
Select Case ComboBox1.Value
Case "GSOP_0286"
Set refrange = Sheets("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
With ActiveSheet
myRow = ActiveCell.Row
Rows(myRow).Select
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End With
Next c

Thanks,

Signature

Brian McCaffery

Reply to this Message

Joel - 29 Aug 2007 11:58 GMT

I made some changes. Changed the functtion from click to change.

Private Sub ComboBox1_Change()

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1
Dim refrange As Range
Dim c As Range
Select Case ComboBox1.Value
Case "GSOP_0286"

Set refrange = Sheets("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
'With ActiveSheet
Sheets("sheet2").Activate
myRow = ActiveCell.Row
Sheets("Sheet2").Rows(myRow).Select
Selection.Copy
Sheets("Report").Activate
Sheets("Report").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
'End With
Next c
End Select
End Sub

> Hello all,
>
[quoted text clipped - 32 lines]
>
> Thanks,

Reply to this Message

Brian - 29 Aug 2007 12:30 GMT

Joel,

Thanks for the reply. It actually did something.

This is my first attempt at using Excel VBA. Wish I hadn't suggested putting
together a mini form of config management.

My error was not explaining everything. The cross references in each range
could be in any one of four sheets. When I click on the cell, I can see the
sheet and column\row reference in the formula bar. Is there a way to select
the sheet to activate after the Application.Double click. If I manually
double click on the cell it takes me to the correct location.

Thanks again

Signature

Brian McCaffery

> I made some changes. Changed the functtion from click to change.
>
[quoted text clipped - 64 lines]
> >
> > Thanks,

Reply to this Message

Joel - 29 Aug 2007 12:56 GMT

I can't answer the question

> Joel,
>
[quoted text clipped - 79 lines]
> > >
> > > Thanks,

Reply to this Message

Tom Ogilvy - 29 Aug 2007 13:20 GMT

Here is a guess at what you are trying to do:

It assumes the formula in the cells of refrange are like

=Sheet3!B9
or
=Sheet3!$B$9

Private Sub ComboBox1_Change()
Dim i as Long, rng as Range
Dim refrange As Range
Dim c As Range

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1

Select Case ComboBox1.Value
Case "GSOP_0286"

Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
s = Replace(c.formula,"=","")
set rng = [s]
rng.entireRow.copy
Sheets("Report").Range("A2") _
.offset(i,0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
Next c
End Select
End Sub

Signature

Regards,
Tom Ogilvy

> Joel,
>
[quoted text clipped - 79 lines]
> > >
> > > Thanks,

Reply to this Message

Brian - 29 Aug 2007 13:48 GMT

Tom, thanks for that. But...

When I run it, I get the message box "Runtime error '424'
In Debug, if I hover over the "set rng" it says "rng=Nothing" If I then
hover over the following [s] it says "GSOPs$A$22" which is the correct
reference for the first in the range.

If I select the definition, it jumps to the rng in the Dim statement.

Any thoughts while I search?

Thanks,

Signature

Brian McCaffery

> Here is a guess at what you are trying to do:
>
[quoted text clipped - 117 lines]
> > > >
> > > > Thanks,

Reply to this Message

Tom Ogilvy - 29 Aug 2007 14:00 GMT

My error,
change that line to

set rng = Evaluate(s)

Signature

Regards,
Tom Ogilvy

> Tom, thanks for that. But...
>
[quoted text clipped - 130 lines]
> > > > >
> > > > > Thanks,

Reply to this Message

Brian - 29 Aug 2007 14:14 GMT

Hooray, nearly there. Thank you for your time, only one minor problem. When
it has copied the last of the range successfully, it then throws up the 424
again, however when in debug it says that "cannot jump to 'Evaluate' because
it is hidden"

Do I need to switch it off before it tries to run when the last c has been
processed?

Sorry to be a pain,

Signature

Brian McCaffery

> My error,
> change that line to
[quoted text clipped - 135 lines]
> > > > > >
> > > > > > Thanks,

Reply to this Message

Tom Ogilvy - 29 Aug 2007 14:36 GMT

Not sure about that error message, but perhaps this:

Private Sub ComboBox1_Change()
Dim i as Long, rng as Range
Dim refrange As Range
Dim c As Range

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1

Select Case ComboBox1.Value
Case "GSOP_0286"

Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
Set rng = Nothing
on Error Resume Next
s = Replace(c.formula,"=","")
set rng = Evaluate(s)
On Error goto 0
if not rng is nothing then
rng.entireRow.copy
Sheets("Report").Range("A2") _
.offset(i,0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
end if
Next c
End Select
End Sub

Signature

Regards,
Tom Ogilvy

> Hooray, nearly there. Thank you for your time, only one minor problem. When
> it has copied the last of the range successfully, it then throws up the 424
[quoted text clipped - 145 lines]
> > > > > > >
> > > > > > > Thanks,

Reply to this Message

Brian - 29 Aug 2007 14:44 GMT

Tom,

Thanks for all your help. I was just about to post that I had found a
solution when I saw your last post. It all works a treat now. I added an If
c.value ="" then end Else Etc.

Thank you and Joel for taking the time to help. My wife, an excel VBA guru,
has been unavailable to help me this last week and a bit, but I have achieved
quite a lot with the help I have recieved here.

All the best,

Signature

Brian McCaffery

> Not sure about that error message, but perhaps this:
>
[quoted text clipped - 182 lines]
> > > > > > > >
> > > > > > > > Thanks,

Reply to this Message