Excel Form annoyance
 

I am using VBA to create a new work sheet with heading A1:D1
Select.range("A1:D1") 'as my NEW list

activesheet.showdataform ' to start inputting data

I get this error msg

""microsoft" office cannot determine which row in your list or
selection contains labels"

If I say OK everything is OK

What I should do to prevent excel from giving me this warning msg
I tried "on error...." but still get the msg

Can you help.

PS It happens only with an Empty list !!!!!

Excel Form annoyance
 

Hi,

Try this:

Sub test()
Application.DisplayAlerts = False
Range("A1:D1").Select
ActiveSheet.ShowDataForm
Application.DisplayAlerts = True
End Sub

Regards,
KL
"Arishy" wrote in message
oups.com...
I am using VBA to create a new work sheet with heading A1:D1
Select.range("A1:D1") 'as my NEW list

activesheet.showdataform ' to start inputting data

I get this error msg

""microsoft" office cannot determine which row in your list or
selection contains labels"

If I say OK everything is OK

What I should do to prevent excel from giving me this warning msg
I tried "on error...." but still get the msg

Can you help.

PS It happens only with an Empty list !!!!!

Excel Form annoyance
 

Hi Arishy,

Try using Application.DisplayAlerts = False, as in my response to your
earlier thread.

As in that thread, ensure that this setting is subsequently,restored to True


---
Regards,
Norman



"Arishy" wrote in message
oups.com...
I am using VBA to create a new work sheet with heading A1:D1
Select.range("A1:D1") 'as my NEW list

activesheet.showdataform ' to start inputting data

I get this error msg

""microsoft" office cannot determine which row in your list or
selection contains labels"

If I say OK everything is OK

What I should do to prevent excel from giving me this warning msg
I tried "on error...." but still get the msg

Can you help.

PS It happens only with an Empty list !!!!!

Excel Form annoyance
 

Hi Arishy

I think what you want to do is to make a new named range in VBA

There are three things you need to know.

The name of the range to be held in A1:D1.

The name of the worksheet you want - it sounds like you want to call it NEW
in your explanation or A1:D1

something like

Set rng = Range("A1:D1")

For Each c In rng
ThisWorkbook.Names.Add "New"
Next c

also see the following two examples for creating the sheets

Sub AddSheetWithNameCheckIfExists()
Dim ws As Worksheet
Dim newSheetName As String
newSheetName = Sheets(1).Range("A1") ' Substitute your range here
For Each ws In Worksheets
If ws.Name = newSheetName Or newSheetName = "" Or
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If
Next
Sheets.Add Type:="Worksheet"
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
.Name = newSheetName
End With
End Sub


Sub Add_Sheet()
Dim wSht As Worksheet
Dim shtName As String
shtName = Format(Now, "mmmm_yyyy")
For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists...Make necessary " & _
"corrections and try again."
Exit Sub
End If
Next wSht
Sheets.Add.Name = shtName
Sheets(shtName).Move After:=Sheets(Sheets.Count)
Sheets("Sheet1").Range("A1:A5").Copy _
Sheets(shtName).Range("A1")
End Sub


hth

Rob
"Arishy" wrote in message
oups.com...
I am using VBA to create a new work sheet with heading A1:D1
Select.range("A1:D1") 'as my NEW list

activesheet.showdataform ' to start inputting data

I get this error msg

""microsoft" office cannot determine which row in your list or
selection contains labels"

If I say OK everything is OK

What I should do to prevent excel from giving me this warning msg
I tried "on error...." but still get the msg

Can you help.

PS It happens only with an Empty list !!!!!

Excel Form annoyance
 

Hi,

try this:

Sub Test_DataForm()
Range("A1:D1").Select
ActiveSheet.Names.Add "Database", _
"=" & Intersect(ActiveSheet.Range(Selection, _
Selection.EntireColumn.Rows(ActiveSheet.Rows.Count )), _
Selection.CurrentRegion).Address
ActiveSheet.ShowDataForm
'ExecuteExcel4Macro "ERROR(TRUE)+DATA.FORM()"
End Sub

Data form uses the name 'Database'. (makes it wide/narrow,
and deletes it when there is no data...)

not very important but 'DisplayAlerts = False' turns off the
warning of deletion, etc. while showing the form.

--
HTH,

okaizawa


Arishy wrote:
I am using VBA to create a new work sheet with heading A1:D1
Select.range("A1:D1") 'as my NEW list

activesheet.showdataform ' to start inputting data

I get this error msg

""microsoft" office cannot determine which row in your list or
selection contains labels"

If I say OK everything is OK

What I should do to prevent excel from giving me this warning msg
I tried "on error...." but still get the msg

Can you help.

PS It happens only with an Empty list !!!!!

Excel Form annoyance
 

Why not just

Sub Test_Dataform()
Range("A1:D1").CurrentRegion.Name = "Database"
ActiveSheet.ShowDataForm
End Sub

Assuming There are headings in A1:D1


--
Regards,
Tom Ogilvy


"okaizawa" wrote in message
...
Hi,

try this:

Sub Test_DataForm()
Range("A1:D1").Select
ActiveSheet.Names.Add "Database", _
"=" & Intersect(ActiveSheet.Range(Selection, _
Selection.EntireColumn.Rows(ActiveSheet.Rows.Count )), _
Selection.CurrentRegion).Address
ActiveSheet.ShowDataForm
'ExecuteExcel4Macro "ERROR(TRUE)+DATA.FORM()"
End Sub

Data form uses the name 'Database'. (makes it wide/narrow,
and deletes it when there is no data...)

not very important but 'DisplayAlerts = False' turns off the
warning of deletion, etc. while showing the form.

--
HTH,

okaizawa


Arishy wrote:
I am using VBA to create a new work sheet with heading A1:D1
Select.range("A1:D1") 'as my NEW list

activesheet.showdataform ' to start inputting data

I get this error msg

""microsoft" office cannot determine which row in your list or
selection contains labels"

If I say OK everything is OK

What I should do to prevent excel from giving me this warning msg
I tried "on error...." but still get the msg

Can you help.

PS It happens only with an Empty list !!!!!

Excel Form annoyance
 

Very elegant solution How did you manage to make the error msg
disappear. Magical

*** Sent via Developersdex http://www.developersdex.com ***

Excel Form annoyance
 

Small IS realy beutiful....You are great Guy's.
It is now "fun" to have problems ;)

Thank you for your solution.

*** Sent via Developersdex http://www.developersdex.com ***

Excel Form annoyance
 

Tom Ogilvy wrote:
Why not just

Sub Test_Dataform()
Range("A1:D1").CurrentRegion.Name = "Database"
ActiveSheet.ShowDataForm
End Sub

Assuming There are headings in A1:D1

i wondered if there was something in column E.
but i might have done an unnecessary thing.
if CurrentRegion is enough, this will work, too:

Range("A1").CurrentRegion.Name = "'" & ActiveSheet.Name & "'!Database"

the name 'Database' should be a sheet level name, or we should delete it
after closing the form. if a workbook has multiple sheets and a workbook
level name 'Database', Data Form on other sheets may not be displayed.

--
HTH,

okaizawa

Excel Form annoyance
 

No data in "E1". It is a "fresh" worksheet creating earlier in the VBA

Excel Form annoyance
 

Sub Test_Dataform()
Range("A1").CurrentRegion.Resize(,4).Name = "'" & _
ActiveSheet.Name & "'!Database"
ActiveSheet.ShowDataForm
End Sub

Would handle the data in E1 or even if there were no data in E1. Also
included your new advice on using sheet level name.
--
Regards,
Tom Ogilvy


"okaizawa" wrote in message
...
Tom Ogilvy wrote:
Why not just

Sub Test_Dataform()
Range("A1:D1").CurrentRegion.Name = "Database"
ActiveSheet.ShowDataForm
End Sub

Assuming There are headings in A1:D1

i wondered if there was something in column E.
but i might have done an unnecessary thing.
if CurrentRegion is enough, this will work, too:

Range("A1").CurrentRegion.Name = "'" & ActiveSheet.Name & "'!Database"

the name 'Database' should be a sheet level name, or we should delete it
after closing the form. if a workbook has multiple sheets and a workbook
level name 'Database', Data Form on other sheets may not be displayed.

--
HTH,

okaizawa