Variable from Userform to Macro

[Vikesh Jain]

Dear all,
Currently I have made a macro. the text of the code is as follows:

Sub ExcelChart2Word()
Dim lm As Single
Dim tm As Single
Dim w As Single
Dim h As Single

w = Application.InputBox("What should be the Width of Word chart?", , ,
, , , , 1)
h = Application.InputBox("What should be the Height of Word chart?", ,
, , , , , 1)

If w <= 0 Or h <= 0 Then
MsgBox ("Action cancelled")
Exit Sub
Else
End If
lm = (11 - w) / 2
tm = (8.5 - h) / 2
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.AutoScaleFont = False
With ActiveChart.PageSetup
.LeftMargin = Application.InchesToPoints(lm)
.RightMargin = Application.InchesToPoints(lm)
.TopMargin = Application.InchesToPoints(tm)
.BottomMargin = Application.InchesToPoints(tm)

End With

My query is that can someone help me with a code whereby instead of 2
inputboxes, I can ask the user to input the data on onego e.g. a single
userform. I have never used a userform before. Any help would be
appreciated.

Thanks in advance.
Vikesh Jain

[Mark Driscol]

There are many books that discuss this. Check out one of John
Walkenbach's Power Programming books, for example, on how to set up and
use a UserForm.

Mark


Vikesh Jain wrote:
> Dear all,
> Currently I have made a macro. the text of the code is as follows:
>
> Sub ExcelChart2Word()
> Dim lm As Single
> Dim tm As Single
> Dim w As Single
> Dim h As Single
>
> w = Application.InputBox("What should be the Width of Word chart?", , ,
> , , , , 1)
> h = Application.InputBox("What should be the Height of Word chart?", ,
> , , , , , 1)
>
> If w <= 0 Or h <= 0 Then
> MsgBox ("Action cancelled")
> Exit Sub
> Else
> End If
> lm = (11 - w) / 2
> tm = (8.5 - h) / 2
> ActiveChart.Location Where:=xlLocationAsNewSheet
> ActiveChart.ChartArea.Select
> ActiveChart.ChartArea.AutoScaleFont = False
> With ActiveChart.PageSetup
> .LeftMargin = Application.InchesToPoints(lm)
> .RightMargin = Application.InchesToPoints(lm)
> .TopMargin = Application.InchesToPoints(tm)
> .BottomMargin = Application.InchesToPoints(tm)
>
> End With
>
> My query is that can someone help me with a code whereby instead of 2
> inputboxes, I can ask the user to input the data on onego e.g. a single
> userform. I have never used a userform before. Any help would be
> appreciated.
>
> Thanks in advance.
> Vikesh Jain

[Vikesh Jain]

Dear Mark,
Actually i was already planning to do that, however if somebody may
help me with the code!
I know it must be simple, but a live example in front of me would help
me better.

Thanks

Vikesh

Mark Driscol wrote:
> There are many books that discuss this. Check out one of John
> Walkenbach's Power Programming books, for example, on how to set up and
> use a UserForm.
>
> Mark