Greetings,

I am having problems passing arguments from a BeforeDoubleClick event.
The problem is that out of five argument that the called procedure is
looking for, only 2 will always have a value. Of the other 3 one or
more will not have a value depending on what cell is DoubleClicked.
This all worked before I moved the UserForm and Module1 from each
schedule workbook to only one set in the customer record workbook. At
first, I was getting a type mismatch, but now I am getting nothing (no
errors and no activity, nothing)

Here is my code in the sheet section of the calling workbook:

Option Explicit
Dim OtherWkbk As Workbook

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Dim sInv As String
Set OtherWkbk = Workbooks("MCL6.xls")
Select Case Target.Column
Case 3
Select Case Target.Row
Case 3 To 839
sInv = CStr(Target.Value)
End Select
Case Else
sInv = 0
End Select
Application.Run "'" & OtherWkbk.Name & "'!DoubleClickAction", _
CInt(Target.Column), _
CInt(Target.Row), _
ThisWorkbook.Name, _
sInv, _
Day(Range("A" & Target.Row).MergeArea.Cells(1))
Cancel = True
End Sub

In Module 1 on other workbook:

Option Explicit
Public iTD As Integer
Public wb1 As Workbook

Public Sub DoubleClickAction(ByVal _
TCol As Integer, _
TRow As Integer, _
WhichWkbk As Workbook, _
TVal As String, _
WhichDay As Date)

Set iTD = Day(WhichDay)
Set wb1 = WhichWkbk
Set ws1_1 = wb1.Worksheets("Enter")
....
End Sub

Any body have any ideas on what went wrong?

If you need any other info let me know, I will gladly submit it.

Any help is appreciated.

-Minitman

One thing I see that'll give a mismatch is this

passing: ThisWorkbook.Name ' a string
expecting: WhichWkbk As Workbook ' workbook object

There may be other problems too but start by changing that.

In passing, suggest you change those 'As Integer' to 'As Long'. As Integer
is slightly less efficient in 32bit but more importantly, will get an
overflow if TRow is over 32k.

Regards,
Peter T



"Minitman" wrote in message
...
Greetings,

I am having problems passing arguments from a BeforeDoubleClick event.
The problem is that out of five argument that the called procedure is
looking for, only 2 will always have a value. Of the other 3 one or
more will not have a value depending on what cell is DoubleClicked.
This all worked before I moved the UserForm and Module1 from each
schedule workbook to only one set in the customer record workbook. At
first, I was getting a type mismatch, but now I am getting nothing (no
errors and no activity, nothing)

Here is my code in the sheet section of the calling workbook:

Option Explicit
Dim OtherWkbk As Workbook

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Dim sInv As String
Set OtherWkbk = Workbooks("MCL6.xls")
Select Case Target.Column
Case 3
Select Case Target.Row
Case 3 To 839
sInv = CStr(Target.Value)
End Select
Case Else
sInv = 0
End Select
Application.Run "'" & OtherWkbk.Name & "'!DoubleClickAction", _
CInt(Target.Column), _
CInt(Target.Row), _
ThisWorkbook.Name, _
sInv, _
Day(Range("A" & Target.Row).MergeArea.Cells(1))
Cancel = True
End Sub

In Module 1 on other workbook:

Option Explicit
Public iTD As Integer
Public wb1 As Workbook

Public Sub DoubleClickAction(ByVal _
TCol As Integer, _
TRow As Integer, _
WhichWkbk As Workbook, _
TVal As String, _
WhichDay As Date)

Set iTD = Day(WhichDay)
Set wb1 = WhichWkbk
Set ws1_1 = wb1.Worksheets("Enter")
...
End Sub

Any body have any ideas on what went wrong?

If you need any other info let me know, I will gladly submit it.

Any help is appreciated.

-Minitman

Hey Peter,

Thanks for the reply and your advice, it solved that problem and
exposed a bunch more. I can't seem to make a variable work with this
code. The code works fine to pass as an argument but if I put it on a
variable it errors out

Day(Range("A" & Target.Row).MergeArea.Cells(1))

Which returns the day of interest.

But when I add this code:

Dim vDay

MsgBox "Day is " & Day(Range("A" & Target.Row).MergeArea.Cells(1))
(returns the correct number)

Set vDay = Day(Range("A" & Target.Row).MergeArea.Cells(1))
(returns a type mismatch error)

If I Dim vDay as Integer, Long or Date, I get an object required
error.

What object????

What am I doing wrong??

Any help will be greatly appreciated.

-Minitman

On Sun, 10 Jan 2010 16:32:17 -0000, "Peter T" <peter_t@discussions
wrote:

One thing I see that'll give a mismatch is this

passing: ThisWorkbook.Name ' a string
expecting: WhichWkbk As Workbook ' workbook object

There may be other problems too but start by changing that.

In passing, suggest you change those 'As Integer' to 'As Long'. As Integer
is slightly less efficient in 32bit but more importantly, will get an
overflow if TRow is over 32k.

Regards,
Peter T



"Minitman" wrote in message
.. .
Greetings,

I am having problems passing arguments from a BeforeDoubleClick event.
The problem is that out of five argument that the called procedure is
looking for, only 2 will always have a value. Of the other 3 one or
more will not have a value depending on what cell is DoubleClicked.
This all worked before I moved the UserForm and Module1 from each
schedule workbook to only one set in the customer record workbook. At
first, I was getting a type mismatch, but now I am getting nothing (no
errors and no activity, nothing)

Here is my code in the sheet section of the calling workbook:

Option Explicit
Dim OtherWkbk As Workbook

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Dim sInv As String
Set OtherWkbk = Workbooks("MCL6.xls")
Select Case Target.Column
Case 3
Select Case Target.Row
Case 3 To 839
sInv = CStr(Target.Value)
End Select
Case Else
sInv = 0
End Select
Application.Run "'" & OtherWkbk.Name & "'!DoubleClickAction", _
CInt(Target.Column), _
CInt(Target.Row), _
ThisWorkbook.Name, _
sInv, _
Day(Range("A" & Target.Row).MergeArea.Cells(1))
Cancel = True
End Sub

In Module 1 on other workbook:

Option Explicit
Public iTD As Integer
Public wb1 As Workbook

Public Sub DoubleClickAction(ByVal _
TCol As Integer, _
TRow As Integer, _
WhichWkbk As Workbook, _
TVal As String, _
WhichDay As Date)

Set iTD = Day(WhichDay)
Set wb1 = WhichWkbk
Set ws1_1 = wb1.Worksheets("Enter")
...
End Sub

Any body have any ideas on what went wrong?

If you need any other info let me know, I will gladly submit it.

Any help is appreciated.

-Minitman

Set vDay = Day(Range("A" & Target.Row).MergeArea.Cells(1))
(returns a type mismatch error)

For sure that would give a mismatch error. The expression Day(etc returns a
value, not an object. Simply remove the 'Set', which would only use if
assigning an object to a variable.

Regards,
Peter T


"Minitman" wrote in message
...
Hey Peter,

Thanks for the reply and your advice, it solved that problem and
exposed a bunch more. I can't seem to make a variable work with this
code. The code works fine to pass as an argument but if I put it on a
variable it errors out

Day(Range("A" & Target.Row).MergeArea.Cells(1))

Which returns the day of interest.

But when I add this code:

Dim vDay

MsgBox "Day is " & Day(Range("A" & Target.Row).MergeArea.Cells(1))
(returns the correct number)

Set vDay = Day(Range("A" & Target.Row).MergeArea.Cells(1))
(returns a type mismatch error)

If I Dim vDay as Integer, Long or Date, I get an object required
error.

What object????

What am I doing wrong??

Any help will be greatly appreciated.

-Minitman

On Sun, 10 Jan 2010 16:32:17 -0000, "Peter T" <peter_t@discussions
wrote:

One thing I see that'll give a mismatch is this

passing: ThisWorkbook.Name ' a string
expecting: WhichWkbk As Workbook ' workbook object

There may be other problems too but start by changing that.

In passing, suggest you change those 'As Integer' to 'As Long'. As Integer
is slightly less efficient in 32bit but more importantly, will get an
overflow if TRow is over 32k.

Regards,
Peter T



"Minitman" wrote in message
. ..
Greetings,

I am having problems passing arguments from a BeforeDoubleClick event.
The problem is that out of five argument that the called procedure is
looking for, only 2 will always have a value. Of the other 3 one or
more will not have a value depending on what cell is DoubleClicked.
This all worked before I moved the UserForm and Module1 from each
schedule workbook to only one set in the customer record workbook. At
first, I was getting a type mismatch, but now I am getting nothing (no
errors and no activity, nothing)

Here is my code in the sheet section of the calling workbook:

Option Explicit
Dim OtherWkbk As Workbook

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Dim sInv As String
Set OtherWkbk = Workbooks("MCL6.xls")
Select Case Target.Column
Case 3
Select Case Target.Row
Case 3 To 839
sInv = CStr(Target.Value)
End Select
Case Else
sInv = 0
End Select
Application.Run "'" & OtherWkbk.Name & "'!DoubleClickAction", _
CInt(Target.Column), _
CInt(Target.Row), _
ThisWorkbook.Name, _
sInv, _
Day(Range("A" & Target.Row).MergeArea.Cells(1))
Cancel = True
End Sub

In Module 1 on other workbook:

Option Explicit
Public iTD As Integer
Public wb1 As Workbook

Public Sub DoubleClickAction(ByVal _
TCol As Integer, _
TRow As Integer, _
WhichWkbk As Workbook, _
TVal As String, _
WhichDay As Date)

Set iTD = Day(WhichDay)
Set wb1 = WhichWkbk
Set ws1_1 = wb1.Worksheets("Enter")
...
End Sub

Any body have any ideas on what went wrong?

If you need any other info let me know, I will gladly submit it.

Any help is appreciated.

-Minitman

Thanks Peter.

That was so simple I couldn't find it anywhere. :^

-Minitman

On Thu, 14 Jan 2010 19:27:57 -0000, "Peter T" <peter_t@discussions
wrote:

Set vDay = Day(Range("A" & Target.Row).MergeArea.Cells(1))
(returns a type mismatch error)

For sure that would give a mismatch error. The expression Day(etc returns a
value, not an object. Simply remove the 'Set', which would only use if
assigning an object to a variable.

Regards,
Peter T


"Minitman" wrote in message
.. .
Hey Peter,

Thanks for the reply and your advice, it solved that problem and
exposed a bunch more. I can't seem to make a variable work with this
code. The code works fine to pass as an argument but if I put it on a
variable it errors out

Day(Range("A" & Target.Row).MergeArea.Cells(1))

Which returns the day of interest.

But when I add this code:

Dim vDay

MsgBox "Day is " & Day(Range("A" & Target.Row).MergeArea.Cells(1))
(returns the correct number)

Set vDay = Day(Range("A" & Target.Row).MergeArea.Cells(1))
(returns a type mismatch error)

If I Dim vDay as Integer, Long or Date, I get an object required
error.

What object????

What am I doing wrong??

Any help will be greatly appreciated.

-Minitman

On Sun, 10 Jan 2010 16:32:17 -0000, "Peter T" <peter_t@discussions
wrote:

One thing I see that'll give a mismatch is this

passing: ThisWorkbook.Name ' a string
expecting: WhichWkbk As Workbook ' workbook object

There may be other problems too but start by changing that.

In passing, suggest you change those 'As Integer' to 'As Long'. As Integer
is slightly less efficient in 32bit but more importantly, will get an
overflow if TRow is over 32k.

Regards,
Peter T



"Minitman" wrote in message
...
Greetings,

I am having problems passing arguments from a BeforeDoubleClick event.
The problem is that out of five argument that the called procedure is
looking for, only 2 will always have a value. Of the other 3 one or
more will not have a value depending on what cell is DoubleClicked.
This all worked before I moved the UserForm and Module1 from each
schedule workbook to only one set in the customer record workbook. At
first, I was getting a type mismatch, but now I am getting nothing (no
errors and no activity, nothing)

Here is my code in the sheet section of the calling workbook:

Option Explicit
Dim OtherWkbk As Workbook

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Dim sInv As String
Set OtherWkbk = Workbooks("MCL6.xls")
Select Case Target.Column
Case 3
Select Case Target.Row
Case 3 To 839
sInv = CStr(Target.Value)
End Select
Case Else
sInv = 0
End Select
Application.Run "'" & OtherWkbk.Name & "'!DoubleClickAction", _
CInt(Target.Column), _
CInt(Target.Row), _
ThisWorkbook.Name, _
sInv, _
Day(Range("A" & Target.Row).MergeArea.Cells(1))
Cancel = True
End Sub

In Module 1 on other workbook:

Option Explicit
Public iTD As Integer
Public wb1 As Workbook

Public Sub DoubleClickAction(ByVal _
TCol As Integer, _
TRow As Integer, _
WhichWkbk As Workbook, _
TVal As String, _
WhichDay As Date)

Set iTD = Day(WhichDay)
Set wb1 = WhichWkbk
Set ws1_1 = wb1.Worksheets("Enter")
...
End Sub

Any body have any ideas on what went wrong?

If you need any other info let me know, I will gladly submit it.

Any help is appreciated.

-Minitman