Hi there,

I've got a model which I need to run many times, and I want to script
it to save my having to sit here doing it by hand...

the data which the model runs on is entered on one of the sheets (the
sheet is called "select"). On the basis of data entered by the user, a
button on this sheet is hit, and the model runs...


I want to automate the filling in of this sheet with all the
appropriate data with a script that loops so I don't have to *be* at
the computer to hit the button... This I have done


What I'm stuck on is how to automate the hitting of the button! is this
possible?

for example, how can I call CommandButton1_click() from another macro?

I want to do something like the following

sheet("SELECT").commandbutton1_click()

but it doesn't like it!


Hope this makes sense

cheers

chris

to answer my own question...

obviously make sure that the click method is not private before posting
on newsgroups :)

Cheers anyway

Chris

wrote:
Hi there,

I've got a model which I need to run many times, and I want to script
it to save my having to sit here doing it by hand...

the data which the model runs on is entered on one of the sheets (the
sheet is called "select"). On the basis of data entered by the user, a
button on this sheet is hit, and the model runs...


I want to automate the filling in of this sheet with all the
appropriate data with a script that loops so I don't have to *be* at
the computer to hit the button... This I have done


What I'm stuck on is how to automate the hitting of the button! is this
possible?

for example, how can I call CommandButton1_click() from another macro?

I want to do something like the following

sheet("SELECT").commandbutton1_click()

but it doesn't like it!


Hope this makes sense

cheers

chris

An alternative is to have the code for the button run another macro.
eg...

CommandButton1_Click()

RunThisMacro
End Sub

Sub RunThisMacro()

Your code

End Sub

Sub AnotherMacro()

RunThisMacro
End Sub



On Oct 24, 4:04 pm, wrote:
Hi there,

I've got a model which I need to run many times, and I want to script
it to save my having to sit here doing it by hand...

the data which the model runs on is entered on one of the sheets (the
sheet is called "select"). On the basis of data entered by the user, a
button on this sheet is hit, and the model runs...

I want to automate the filling in of this sheet with all the
appropriate data with a script that loops so I don't have to *be* at
the computer to hit the button... This I have done

What I'm stuck on is how to automate the hitting of the button! is this
possible?

for example, how can I call CommandButton1_click() from another macro?

I want to do something like the following

sheet("SELECT").commandbutton1_click()

but it doesn't like it!

Hope this makes sense

cheers

chris

You don't need to click the button. What is the code that the button runs?
Just add that to your macro.

Regards,
Paul

wrote in message
oups.com...
Hi there,

I've got a model which I need to run many times, and I want to script
it to save my having to sit here doing it by hand...

the data which the model runs on is entered on one of the sheets (the
sheet is called "select"). On the basis of data entered by the user, a
button on this sheet is hit, and the model runs...


I want to automate the filling in of this sheet with all the
appropriate data with a script that loops so I don't have to *be* at
the computer to hit the button... This I have done


What I'm stuck on is how to automate the hitting of the button! is this
possible?

for example, how can I call CommandButton1_click() from another macro?

I want to do something like the following

sheet("SELECT").commandbutton1_click()

but it doesn't like it!


Hope this makes sense

cheers

chris

the button code was already written - and it contained a lot more was
more than simply running another macro - otherwise you're both quite
right, I could have simply run that code :)

Darren is right - in the future, it would be preferable to have a
buttons code running only a single macro...

Thanks for taking the time to reply!

Chris

PCLIVE (RemoveThis) wrote:
You don't need to click the button. What is the code that the button runs?
Just add that to your macro.

Regards,
Paul

wrote in message
oups.com...
Hi there,

I've got a model which I need to run many times, and I want to script
it to save my having to sit here doing it by hand...

the data which the model runs on is entered on one of the sheets (the
sheet is called "select"). On the basis of data entered by the user, a
button on this sheet is hit, and the model runs...


I want to automate the filling in of this sheet with all the
appropriate data with a script that loops so I don't have to *be* at
the computer to hit the button... This I have done


What I'm stuck on is how to automate the hitting of the button! is this
possible?

for example, how can I call CommandButton1_click() from another macro?

I want to do something like the following

sheet("SELECT").commandbutton1_click()

but it doesn't like it!


Hope this makes sense

cheers

chris

Hi Chris,

It appears that you want to have this macro run at various times without the
need to manually click a button. You could do something like put the call to
the macro
in the Worksheet_Change event code... Like so ...

Public Sub Worksheet_Change(ByVal Target as Range)

Dim somePeriod as Long
Dim someThreshold as Long

someThreshold = 5 ' arbitrary

somePeriod = now

if Now % somePeriod someThreshold
call macroName()
end if


End Sub

The worksheet_Change is an automatic event handler that gets called whenever
you make any change to a particular worksheet. Alt+F11 and click on the
worksheet in the window browser to find it. There are several other auto
event handler functions to choose from as well.

There are several approaches to calling some macro without the need to
manually push a button. Think the most appropriate in your case is to mess
with the event handler routines. (Most common one is Worksheet_Change)

Hope that helps,
Chad



" wrote:

Hi there,

I've got a model which I need to run many times, and I want to script
it to save my having to sit here doing it by hand...

the data which the model runs on is entered on one of the sheets (the
sheet is called "select"). On the basis of data entered by the user, a
button on this sheet is hit, and the model runs...


I want to automate the filling in of this sheet with all the
appropriate data with a script that loops so I don't have to *be* at
the computer to hit the button... This I have done


What I'm stuck on is how to automate the hitting of the button! is this
possible?

for example, how can I call CommandButton1_click() from another macro?

I want to do something like the following

sheet("SELECT").commandbutton1_click()

but it doesn't like it!


Hope this makes sense

cheers

chris