Excel macro ~ open form ~ new record
 

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?

Excel macro ~ open form ~ new record
 

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

Excel macro ~ open form ~ new record
 

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

Excel macro ~ open form ~ new record
 

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

Excel macro ~ open form ~ new record
 

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