Excel macro ~ open form ~ new record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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.
 
When I used the alt-w it worked fine. I thought you were creating keyboard
shortcut. Thanks
 
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?
 
Back
Top