macros

[Carrie]

hi.

how do i make a macro to open up the form...for an excel spreadsheet with
data being entered? I tried making a new macro..then hitting the form
button...
and the macro said activeworksheet.open....I forgot. anyways it came back
with an error on it.

how do i assign the macro to a form button?

thanks!! Merry Christmas

 

[Bob Phillips]

How about the Data>Form...menu?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Carrie]

Under Data...I don't see form.

How would I correct the macro??
activesheet.showdataform

has a run time error of 1004...don't understand why it does this.

i'm in 2007 excel.

 

[Dave Peterson]

Maybe your data doesn't start in the location excel expects:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

You can use code like this if you know (or can determine) the range:

Option Explicit
Sub testme01()
Dim LastRow as Long
Application.DisplayAlerts = False
With activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
.Range("A4:x" & lastrow).Name = "'" & .Name & "'!database"
.ShowDataForm
End With
Application.DisplayAlerts = True
End Sub

I started in A4 until the last used row in column A, then extended it to columns
A:X of those rows.

 

[Carrie]

Hi,

ok, i see the table...except it's all empty. I'm just a beginner...please
be patient.

my column headings for my items is b5 thru f5.
then all the detail will be b6 thru f6....down to bxxxxx to fxxxxxxx

please tell me where to adjust. i had copied the macro
into my spreadsheet.

thanks

 

[Dave Peterson]

Your table shouldn't be empty--it has to have headers.

Option Explicit
Sub testme01()
Dim LastRow as Long
Application.DisplayAlerts = False
With activesheet
lastrow = .cells(.rows.count,"B").end(xlup).row
.Range("A5:F" & lastrow).Name = "'" & .Name & "'!database"
.ShowDataForm
End With
Application.DisplayAlerts = True
End Sub

 

[RobN]

Hi Dave,

I tested this as I could see some use for myself. Hence my comments.....
1. Shouldn't at least one row of data be already entered within the table
range for the data form to appear? When my data area (other than the
headers) was blank, I too got the error 1004 message.
2. I think the range should be B5:F....... otherwise an empty unnecessary
field will appear at the top of the Data form.

Rob

 

[Dave Peterson]

Thanks for testing.

You're correct. It looks like excel wants at least one line of data. (I
guessed wrong!)

Maybe this would be better:

Option Explicit
Sub testme01()
Dim LastRow As Long
Application.DisplayAlerts = False
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
If LastRow = 5 Then
LastRow = 6
End If
.Range("A5:F" & LastRow).Name = "'" & .Name & "'!database"
.ShowDataForm
End With
Application.DisplayAlerts = True
End Sub

 

[Dave Peterson]

ps. Yep. And that line should have been:

..Range("b5:F" & LastRow).Name = "'" & .Name & "'!database"

(starting in column B)