macro using cell value

J

Jason Falzon

Hello,

I have an application called DPlot that can take commands from excel through
a macro.

I managed to pass information like so: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,-1)]"
DDETerminate Channel

End Sub
Sub UpElevation()
'
' UpElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,1)]"
DDETerminate Channel

End Sub
Sub LeftAzimuth()
'
' LeftAzimuth Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(-1,)]"
DDETerminate Channel

End Sub
Sub RightAzimuth()
'
' RightAzimuth Macro
' Macro recorded 24/03/2010

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(1,)]"
DDETerminate Channel

End Sub

At the moment I created 4 buttons, each of which have 1 of the above macros
assigned. This results in having to click repeatedly until I acheive the
disired result.

Now I'd like to make it moredynamic by substituiting the 1 and -1 values by
a cell value inside the same workbook but possibly different sheet. This cell
will be in turn controlled by a scroll bar inside excel that varies from -180
to 180.

My problem is that I cannot figure out the coding to refer to the cell and
how to use it inside each macro
 
J

Jason Falzon

Thanks Andrea,

I replaced Sheet1 with the name of my sheet and put L1 instead of A1.

So now I have something like this: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
x = Worksheets("3D_Data").Range("L1").Value
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,x)]"
DDETerminate Channel

End Sub

I tried using x and x.Value but the same. I am missing something. Do I need
brackets inverted commas or something?

Andrea Jones said:
x = Worksheets("Sheet1").Range("A1").Value

www.stratatraining.co.uk

Jason Falzon said:
Hello,

I have an application called DPlot that can take commands from excel through
a macro.

I managed to pass information like so: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,-1)]"
DDETerminate Channel

End Sub
Sub UpElevation()
'
' UpElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,1)]"
DDETerminate Channel

End Sub
Sub LeftAzimuth()
'
' LeftAzimuth Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(-1,)]"
DDETerminate Channel

End Sub
Sub RightAzimuth()
'
' RightAzimuth Macro
' Macro recorded 24/03/2010

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(1,)]"
DDETerminate Channel

End Sub

At the moment I created 4 buttons, each of which have 1 of the above macros
assigned. This results in having to click repeatedly until I acheive the
disired result.

Now I'd like to make it moredynamic by substituiting the 1 and -1 values by
a cell value inside the same workbook but possibly different sheet. This cell
will be in turn controlled by a scroll bar inside excel that varies from -180
to 180.

My problem is that I cannot figure out the coding to refer to the cell and
how to use it inside each macro
Click to expand...
Click to expand...
 
L

Luke M

You need some additional quotation marks to seperate the text from a
variable.
x = Worksheets("3D_Data").Range("L1").Value
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(," & x & ")]"
DDETerminate Channel
--
Best Regards,

Luke M
Jason Falzon said:
Thanks Andrea,

I replaced Sheet1 with the name of my sheet and put L1 instead of A1.

So now I have something like this: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
x = Worksheets("3D_Data").Range("L1").Value
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,x)]"
DDETerminate Channel

End Sub

I tried using x and x.Value but the same. I am missing something. Do I
need
brackets inverted commas or something?

Andrea Jones said:
x = Worksheets("Sheet1").Range("A1").Value

www.stratatraining.co.uk

Jason Falzon said:
Hello,

I have an application called DPlot that can take commands from excel
through
a macro.

I managed to pass information like so: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,-1)]"
DDETerminate Channel

End Sub
Sub UpElevation()
'
' UpElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,1)]"
DDETerminate Channel

End Sub
Sub LeftAzimuth()
'
' LeftAzimuth Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(-1,)]"
DDETerminate Channel

End Sub
Sub RightAzimuth()
'
' RightAzimuth Macro
' Macro recorded 24/03/2010

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(1,)]"
DDETerminate Channel

End Sub

At the moment I created 4 buttons, each of which have 1 of the above
macros
assigned. This results in having to click repeatedly until I acheive
the
disired result.

Now I'd like to make it moredynamic by substituiting the 1 and -1
values by
a cell value inside the same workbook but possibly different sheet.
This cell
will be in turn controlled by a scroll bar inside excel that varies
from -180
to 180.

My problem is that I cannot figure out the coding to refer to the cell
and
how to use it inside each macro
Click to expand...
Click to expand...
Click to expand...
 
J

Jason Falzon

Luke and Andrea thanks a million for your help.

Finally I found an acceptable solution with your help and some research.

I finaly used 2 scroll bars one for elevation and one for azimuth. Each
scroll bar controls 1 particular cell, but both have the same macro assigned.
So when I change scroll bar macro runs automatically taking the new values

coding: -


Sub ContourView()
'
' ContourView Macro
' Macro recorded 24/03/2010
'

'
x = Worksheets("3D_Data").Range("L1").Value
y = Worksheets("3D_Data").Range("L2").Value
Dim xNum As Long
xNum = 0
Do While xNum <> 0
Loop

Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourView(" & y & "," & x & ")]"
DDETerminate Channel

End Sub

There might be better solutions but I am more then happy with this for now.

Cheers

Jason



Luke M said:
You need some additional quotation marks to seperate the text from a
variable.
x = Worksheets("3D_Data").Range("L1").Value
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(," & x & ")]"
DDETerminate Channel
--
Best Regards,

Luke M
Jason Falzon said:
Thanks Andrea,

I replaced Sheet1 with the name of my sheet and put L1 instead of A1.

So now I have something like this: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
x = Worksheets("3D_Data").Range("L1").Value
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,x)]"
DDETerminate Channel

End Sub

I tried using x and x.Value but the same. I am missing something. Do I
need
brackets inverted commas or something?

Andrea Jones said:
x = Worksheets("Sheet1").Range("A1").Value

www.stratatraining.co.uk

:

Hello,

I have an application called DPlot that can take commands from excel
through
a macro.

I managed to pass information like so: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,-1)]"
DDETerminate Channel

End Sub
Sub UpElevation()
'
' UpElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,1)]"
DDETerminate Channel

End Sub
Sub LeftAzimuth()
'
' LeftAzimuth Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(-1,)]"
DDETerminate Channel

End Sub
Sub RightAzimuth()
'
' RightAzimuth Macro
' Macro recorded 24/03/2010

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(1,)]"
DDETerminate Channel

End Sub

At the moment I created 4 buttons, each of which have 1 of the above
macros
assigned. This results in having to click repeatedly until I acheive
the
disired result.

Now I'd like to make it moredynamic by substituiting the 1 and -1
values by
a cell value inside the same workbook but possibly different sheet.
This cell
will be in turn controlled by a scroll bar inside excel that varies
from -180
to 180.

My problem is that I cannot figure out the coding to refer to the cell
and
how to use it inside each macro
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro using a cell reference 3
Converting from DAO to ADO confusion 1
Macro & Protected sheet 2
After Macro runs, no cell activated - what am I missing? 2
Macro 5
Macro Pause for selecting cell 3
Macro to populate same cell on multiple worksheets 1
can't paste from clipboard in my macro 1

Top