Is there some way to set a cell value or a public variable in an add-in from
another worksheet that is using the add-in? If so, how? TIA!

Jim

Dim myVal as variant
myval = workbooks("otherworkbook.xls").worksheets("sheet1" ).range("a1").value

If I understood.


polandjc wrote:

Is there some way to set a cell value or a public variable in an add-in from
another worksheet that is using the add-in? If so, how? TIA!

Jim

--

Dave Peterson

Actually I wanted to do the reverse
(workbooks("otherworkbook.xls").worksheets("sheet1 ").range("a1") = myval).

When I tried setting a cell value it ignored the statement without raising
an error. Wondered if perhaps instead I could access a public property.

Jim

"Dave Peterson" wrote:

Dim myVal as variant
myval = workbooks("otherworkbook.xls").worksheets("sheet1" ).range("a1").value

If I understood.


polandjc wrote:

Is there some way to set a cell value or a public variable in an add-in from
another worksheet that is using the add-in? If so, how? TIA!

Jim

--

Dave Peterson

Do you have "on error resume next" turned on when that statement runs?

I've never seen VBA ignore a statement like this.



polandjc wrote:

Actually I wanted to do the reverse
(workbooks("otherworkbook.xls").worksheets("sheet1 ").range("a1") = myval).

When I tried setting a cell value it ignored the statement without raising
an error. Wondered if perhaps instead I could access a public property.

Jim

"Dave Peterson" wrote:

Dim myVal as variant
myval = workbooks("otherworkbook.xls").worksheets("sheet1" ).range("a1").value

If I understood.


polandjc wrote:

Is there some way to set a cell value or a public variable in an add-in from
another worksheet that is using the add-in? If so, how? TIA!

Jim

--

Dave Peterson


--

Dave Peterson

Nope... no error trapping at all. Acts as if it set a value in the
spreadsheet but there's nothing there. I'd rather use a public variable
though as it seems cleaner.

Jim

"Dave Peterson" wrote:

Do you have "on error resume next" turned on when that statement runs?

I've never seen VBA ignore a statement like this.



polandjc wrote:

Actually I wanted to do the reverse
(workbooks("otherworkbook.xls").worksheets("sheet1 ").range("a1") = myval).

When I tried setting a cell value it ignored the statement without raising
an error. Wondered if perhaps instead I could access a public property.

Jim

"Dave Peterson" wrote:

Dim myVal as variant
myval = workbooks("otherworkbook.xls").worksheets("sheet1" ).range("a1").value

If I understood.


polandjc wrote:

Is there some way to set a cell value or a public variable in an add-in from
another worksheet that is using the add-in? If so, how? TIA!

Jim

--

Dave Peterson


--

Dave Peterson

I guess I'm confused.

You have a workbook and an addin.

The code you're running is in which?

The value is in which?

The public variable is in which?



polandjc wrote:

Nope... no error trapping at all. Acts as if it set a value in the
spreadsheet but there's nothing there. I'd rather use a public variable
though as it seems cleaner.

Jim

"Dave Peterson" wrote:

Do you have "on error resume next" turned on when that statement runs?

I've never seen VBA ignore a statement like this.



polandjc wrote:

Actually I wanted to do the reverse
(workbooks("otherworkbook.xls").worksheets("sheet1 ").range("a1") = myval).

When I tried setting a cell value it ignored the statement without raising
an error. Wondered if perhaps instead I could access a public property.

Jim

"Dave Peterson" wrote:

Dim myVal as variant
myval = workbooks("otherworkbook.xls").worksheets("sheet1" ).range("a1").value

If I understood.


polandjc wrote:

Is there some way to set a cell value or a public variable in an add-in from
another worksheet that is using the add-in? If so, how? TIA!

Jim

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

My guess is you have a public variable in (say) workbookA.xls and you have your
code in workbookB.xls and the code is running from workbookB.xls.

If that's correct, put a function in workbooka.xls that looks like:

public myVal as variant

sub auto_open()
myVal = "something that sets that variable"
end sub

'this does half the work...
function getmyVar()
getmyvar = myVal
end function

Then in workbookb.xls, you can use something like

dim myOtherVar as variant
myOthervariable = application.run("workbookA.xls!getmyvar")


Dave Peterson wrote:

I guess I'm confused.

You have a workbook and an addin.

The code you're running is in which?

The value is in which?

The public variable is in which?

polandjc wrote:

Nope... no error trapping at all. Acts as if it set a value in the
spreadsheet but there's nothing there. I'd rather use a public variable
though as it seems cleaner.

Jim

"Dave Peterson" wrote:

Do you have "on error resume next" turned on when that statement runs?

I've never seen VBA ignore a statement like this.



polandjc wrote:

Actually I wanted to do the reverse
(workbooks("otherworkbook.xls").worksheets("sheet1 ").range("a1") = myval).

When I tried setting a cell value it ignored the statement without raising
an error. Wondered if perhaps instead I could access a public property.

Jim

"Dave Peterson" wrote:

Dim myVal as variant
myval = workbooks("otherworkbook.xls").worksheets("sheet1" ).range("a1").value

If I understood.


polandjc wrote:

Is there some way to set a cell value or a public variable in an add-in from
another worksheet that is using the add-in? If so, how? TIA!

Jim

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

And creating the function in one workbook and running it from the other workbook
didn't work?

ps. I'm still confused at what's happening.

polandjc wrote:

Wow Dave, you have amazing patience!

That's close to what I'm trying, but I want to pass from workbookB.xls to
workbookA.xla the value to set myVal to.

Jim

"Dave Peterson" wrote:

My guess is you have a public variable in (say) workbookA.xls and you have your
code in workbookB.xls and the code is running from workbookB.xls.

If that's correct, put a function in workbooka.xls that looks like:

public myVal as variant

sub auto_open()
myVal = "something that sets that variable"
end sub

'this does half the work...
function getmyVar()
getmyvar = myVal
end function

Then in workbookb.xls, you can use something like

dim myOtherVar as variant
myOthervariable = application.run("workbookA.xls!getmyvar")



--

Dave Peterson

OK this is my most recent attempt:

Worksheet A:

Private Sub Workbook_Open()
Application.Run ("WorksheetB.xls!SetName('test')")
End Sub

Worksheet B:

Pubilc sFileName As String
Public Sub SetName(ByVal sName As String)
sFileName = sName
End Sub

Starting the execution in Worksheet A, I get a run time error 1004
(Application-defined or object-defined error).

The additional wrinkle to this is that I want Worksheet B to be a protected
XLA.

Jim

"Dave Peterson" wrote:

And creating the function in one workbook and running it from the other workbook
didn't work?

ps. I'm still confused at what's happening.

Hi Polandic,

Private Sub Workbook_Open()
Application.Run ("WorksheetB.xls!SetName('test')")
End Sub

If the name of the second workbook is WorksheetB.xls, then try:

Private Sub Workbook_Open()

Application.Run "WorksheetB.xls!SetName", "Test"

End Sub


---
Regards,
Norman


"polandjc" wrote in message
...
OK this is my most recent attempt:

Worksheet A:

Private Sub Workbook_Open()
Application.Run ("WorksheetB.xls!SetName('test')")
End Sub

Worksheet B:

Pubilc sFileName As String
Public Sub SetName(ByVal sName As String)
sFileName = sName
End Sub

Starting the execution in Worksheet A, I get a run time error 1004
(Application-defined or object-defined error).

The additional wrinkle to this is that I want Worksheet B to be a
protected
XLA.

Jim

"Dave Peterson" wrote:

And creating the function in one workbook and running it from the other
workbook
didn't work?

ps. I'm still confused at what's happening.

Nope... same error :(

"Norman Jones" wrote:

Hi Polandic,

Private Sub Workbook_Open()
Application.Run ("WorksheetB.xls!SetName('test')")
End Sub

If the name of the second workbook is WorksheetB.xls, then try:

Private Sub Workbook_Open()

Application.Run "WorksheetB.xls!SetName", "Test"

End Sub


---
Regards,
Norman


"polandjc" wrote in message
...
OK this is my most recent attempt:

Worksheet A:

Private Sub Workbook_Open()
Application.Run ("WorksheetB.xls!SetName('test')")
End Sub

Worksheet B:

Pubilc sFileName As String
Public Sub SetName(ByVal sName As String)
sFileName = sName
End Sub

Starting the execution in Worksheet A, I get a run time error 1004
(Application-defined or object-defined error).

The additional wrinkle to this is that I want Worksheet B to be a
protected
XLA.

Jim

"Dave Peterson" wrote:

And creating the function in one workbook and running it from the other
workbook
didn't work?

ps. I'm still confused at what's happening.