Excel macro ~ open form ~ new record

[Bob]

Inventory spreadsheet, bulk raw material in, record date when used, but it's
got to be goof proof, so existing records don't get overwritten.

I have tried:
Sub Macro1()
ActiveSheet.ShowDataForm
End Sub
but this doesn't jump to new record

Sub EnterNewRecord()
DoCmd.OpenForm "RollForm", acNormal
DoCmd.GoToRecord acNewRec
End Sub
I created "RollForm" by: Insert->Name->Define, range a2:I2
But this creates "run-time error 424", Object required.

So, is DoCmd for Access only? Has named form been created incorrectly or
saved wrong?
Any solutions to associating an "Enter button" with macro to open data form
to new record?

[Bob]

What I meant by "enter button", was to assign macro to custom option button.
I don't know if
Application.DisplayAlerts =
is gaining me anything. Take those two lines out, and form still opens, but
not to new record. Leave them in, and I see no change in form.


"Dave Peterson" wrote:

> Maybe something like:
>
> Option Explicit
> Sub testme()
>
> SendKeys "%w"
> Application.DisplayAlerts = False
> ActiveSheet.ShowDataForm
> Application.DisplayAlerts = True
>
> End Sub
>
> (The shortcut for a New record is alt-w (%w in Sendkeys syntax).)
>
> And excel's VBA doesn't have a DoCmd command.
>
> Bob wrote:
> >
> > Inventory spreadsheet, bulk raw material in, record date when used, but it's
> > got to be goof proof, so existing records don't get overwritten.
> >
> > I have tried:
> > Sub Macro1()
> > ActiveSheet.ShowDataForm
> > End Sub
> > but this doesn't jump to new record
> >
> > Sub EnterNewRecord()
> > DoCmd.OpenForm "RollForm", acNormal
> > DoCmd.GoToRecord acNewRec
> > End Sub
> > I created "RollForm" by: Insert->Name->Define, range a2:I2
> > But this creates "run-time error 424", Object required.
> >
> > So, is DoCmd for Access only? Has named form been created incorrectly or
> > saved wrong?
> > Any solutions to associating an "Enter button" with macro to open data form
> > to new record?
>
> --
>
> Dave Peterson
>

[Bob]

When I used the alt-w it worked fine. I thought you were creating keyboard
shortcut. Thanks

"Bob" wrote:

> What I meant by "enter button", was to assign macro to custom option button.
> I don't know if
> Application.DisplayAlerts =
> is gaining me anything. Take those two lines out, and form still opens, but
> not to new record. Leave them in, and I see no change in form.
>
>
> "Dave Peterson" wrote:
>
> > Maybe something like:
> >
> > Option Explicit
> > Sub testme()
> >
> > SendKeys "%w"
> > Application.DisplayAlerts = False
> > ActiveSheet.ShowDataForm
> > Application.DisplayAlerts = True
> >
> > End Sub
> >
> > (The shortcut for a New record is alt-w (%w in Sendkeys syntax).)
> >
> > And excel's VBA doesn't have a DoCmd command.
> >
> > Bob wrote:
> > >
> > > Inventory spreadsheet, bulk raw material in, record date when used, but it's
> > > got to be goof proof, so existing records don't get overwritten.
> > >
> > > I have tried:
> > > Sub Macro1()
> > > ActiveSheet.ShowDataForm
> > > End Sub
> > > but this doesn't jump to new record
> > >
> > > Sub EnterNewRecord()
> > > DoCmd.OpenForm "RollForm", acNormal
> > > DoCmd.GoToRecord acNewRec
> > > End Sub
> > > I created "RollForm" by: Insert->Name->Define, range a2:I2
> > > But this creates "run-time error 424", Object required.
> > >
> > > So, is DoCmd for Access only? Has named form been created incorrectly or
> > > saved wrong?
> > > Any solutions to associating an "Enter button" with macro to open data form
> > > to new record?
> >
> > --
> >
> > Dave Peterson
> >

[Dave Peterson]

The application.displayalerts will hide the message that says excel is having
trouble determining the range. It may not be necessary, but usually doesn't
hurt.

sendkeys "%w"
should have sent the alt-w to the dataform. Are you saying the code worked ok
or you had to hit alt-w manually?


Bob wrote:
>
> When I used the alt-w it worked fine. I thought you were creating keyboard
> shortcut. Thanks
>
> "Bob" wrote:
>
> > What I meant by "enter button", was to assign macro to custom option button.
> > I don't know if
> > Application.DisplayAlerts =
> > is gaining me anything. Take those two lines out, and form still opens, but
> > not to new record. Leave them in, and I see no change in form.
> >
> >
> > "Dave Peterson" wrote:
> >
> > > Maybe something like:
> > >
> > > Option Explicit
> > > Sub testme()
> > >
> > > SendKeys "%w"
> > > Application.DisplayAlerts = False
> > > ActiveSheet.ShowDataForm
> > > Application.DisplayAlerts = True
> > >
> > > End Sub
> > >
> > > (The shortcut for a New record is alt-w (%w in Sendkeys syntax).)
> > >
> > > And excel's VBA doesn't have a DoCmd command.
> > >
> > > Bob wrote:
> > > >
> > > > Inventory spreadsheet, bulk raw material in, record date when used, but it's
> > > > got to be goof proof, so existing records don't get overwritten.
> > > >
> > > > I have tried:
> > > > Sub Macro1()
> > > > ActiveSheet.ShowDataForm
> > > > End Sub
> > > > but this doesn't jump to new record
> > > >
> > > > Sub EnterNewRecord()
> > > > DoCmd.OpenForm "RollForm", acNormal
> > > > DoCmd.GoToRecord acNewRec
> > > > End Sub
> > > > I created "RollForm" by: Insert->Name->Define, range a2:I2
> > > > But this creates "run-time error 424", Object required.
> > > >
> > > > So, is DoCmd for Access only? Has named form been created incorrectly or
> > > > saved wrong?
> > > > Any solutions to associating an "Enter button" with macro to open data form
> > > > to new record?
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson