Free Microsoft Excel 2013 Quick Reference

Dataform

Hi
Having trouble displaying a dataform using a macro.
The macro gives an error on the line showdataform stating that there is a
class error.
What am I doing wrong
Charles


Post your answer or comment

comments powered by Disqus
Hi all, I have the standard dataform showing on my quick access toolbar.
However for novice users I'd like my macro assigned to a button to take user to sheet with a blank dbase initially. This dbase is located with headers at A5:K5
When I do this manually I'd goto A6 (FIRST LINE OF DBASE) and click dataform icon. Form appears as required. Can't achieve this by coded method though ?

Be aware that data exists in cells A1:K4 ??
Also, because this dbase is for registrations to a competition cells B6:F6 have formulas which (Index Match) to a competitor dbase on separate sheet. This allows user to simply enter ID NO. into A6 via user form and Name(B6) Address(C6) Phone(D6) etc will populate AUTOMATICALLY after user form closed. Hope that all makes sense and when I do this manually it all works fine.

Problem is simply that I'd like novice users to click a text box button and be taken to rego Dbase and automatically be in A6 AND SEE BUILT IN USERFORM READY TO GO.

Can it be done and if so how please.

Many thanx

Russco

Good day:

I have, to launch DataForm:


	VB:
	
 CommandButton1_Click() 
    Range("A6").CurrentRegion.Select Application.CommandBars.FindControl(ID:=860).Execute 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And this to perform a "Sort" upon WorkSheet_Deactivate:


	VB:
	
 Worksheet_Deactivate() 
    Dim LastRow As Long 
    LastRow = Range("A" & Rows.Count).End(xlUp).Row 
    Range("A6").Sort Key1:=Range("A6"), Order1:=xlAscending, Header:= _ 
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Is it possible to have the WorkSheet_Deactivate action tied into the code for the "Close" button on the DataForm?

All help is greatly appreciated.

HOW do you write the code to tell the add Button to save the data from a dataform to the next row available each this the button is pushed

I have a spreadsheet used as a database. Upon open or activation I want to
show the dataform automatically. Upon close or deactivation of the worksheet
I want it to close automatically.

My code on closing or deactivation doesn't work. You have to physically
select the close button on the form or use Alt-L for the keyboard shortcut.
Apparently the open form precludes the macros.

Can I close the form automatically and, if so, how?

My code (in ThisWorkbook)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
SendKeys "%{L}", True
End Sub

Private Sub Workbook_Open()
On Error Resume Next
ActiveSheet.ShowDataForm
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error Resume Next
ActiveSheet.ShowDataForm
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
SendKeys "%{L}", True
End Sub

I created a dataform macro in Excel with the Auto_Open
name. I need the dataform to be for just the headings in
columns A-G, although there are column headings in A-P, H-
P aren't needed for the average data entry person. Does
anyone have any suggestions?

I also need to have a column G sort ascending upon close,
Auto_Close macro. I got it started, but since Row 1 is a
merged cell, and isn't included in the macro, I keep
getting a debugging error. Need to sort G2--end of data.
Any suggestions?

I can provide copies of the macro wording that I tried to
use if you need to see it.

Thanks!
Beth

Hi,
Code:
Range("A1").Select
ActiveSheet.ShowDataForm

The above code shows up the dataform in an excel worksheet. But how it can
be written in such a way so that the data form is ready to accept new
record.(Or like the "New" tab clicked.) I hope the question is clear.

From a command button on a dataform the "on click" event,
can I run code from a Standard module?

Totally, separate Q:
In a standard module
where I use
Dim LrowS2 as Integer
and later in the code I have a line:
LrowS2 = Range("B65356").end(xlUp).row

I get compile error at this point, with explanation of:
Statement Invalid outside Type Block..
???? Not a clue here....

How do you stop a user from putting back a menu (stop the user from
customize the menu)
is there any way to stop a user from using an excel feature. I am
interested in stoping them from using dataforms.

Hi All,

I've done this with J-walk dataform but can't find if or how to resize the
built-in dataform in Excel. Any help would be greatly appreciated.

TIA,

Don

Hi All,

I have a small problem (again) that requires an expert eye.

I have this code in the ThisWorkbook, WorkBook_Open:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
    UserForm2.Show
    Sheets("All Tasks").Protect Password:="xxxx", _
            UserInterFaceOnly:=True
Application.ScreenUpdating = True
End Sub
which should, and does allow all the macros to run on the sheet. Great. However, on the same sheet I have a macro that calls the dataform, and then sorts the sheet upon dataform closure:

Sub Update() ' use with button Update Tasks on
info sheet
    Sheet1.Visible = True
    Sheet1.Activate
    ActiveSheet.ShowDataForm
    With Application
        .ScreenUpdating = False
        .Goto Sheet1.Range("A3")
    End With
    Range("A3").Select
            Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Key2:=Range("E3") _
            , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
            False, Orientation:=xlTopToBottom
    Call Update_All
    Sheet1.Visible = False
    Sheet2.Activate
    Application.ScreenUpdating = True
End Sub
My problem is that once the macro finishes and I go back to view sheet1 and find that it is now unprotected.

In fact, just checking the other macros that interact with this sheet, they all seem to leave the sheet unprotected when they have finished.

My solution would be to re-protect the sheet in all of the macros, but I thought the whole point of the usefInterfaceOnly option was that I did not have to do this?

Has anyone got any solutions?

many thanks for your time.

JD

Good Morning,

I want to use the VBA Userform to create a personalised userform, that works in exactly the same way the standard dataform does in Excel (i.e. when you select Data->Form).

I want MY userform to be able to cycle through all records within a database, and create new ones. However, I cannot use the standard data form as this does not present the information appropriately.

Regards

Howardjo

I am trying to set up a simple recorded macros to only show the dataform. I
have database-style data on Sheet 2 and 3. I would like buttons on sheet 1
that only shows the data form for the requested sheet. I have fallen at the
first hurdle as although I can manually get the form, when I record the same
process:-
....Application.Goto Reference:="start"
.....ActiveSheet.ShowDataForm
I get this:-
Run time error 1004. ShowdataForm method of Worksheet class failed! I
have all the required object libraries switched on.
Any help please...

Hi,
Code:
Range("A1").Select
ActiveSheet.ShowDataForm

The above code shows up the dataform in an excel worksheet. But how it can
be written in such a way so that the data form is ready to accept new
record.(Or like the "New" tab clicked.) I hope the question is clear.

My Systems:
1) Apple MacBook OSX 10+ using Excel 2000
2) PC Notebook with XP using Excel 2000

The following occurs in both systems...

I am adding data to a database using DataForm. Three of the fields within the database are Dates. I have formatted the cells on the worksheet as 'dd mmm yyyy'.

When using the DataForm from within the Worksheets all is fine, the format within the dataform shows as it has been inserted, 'dd/mm/yy'

However, having created a macro to show the DataForm for input, and despite having entered the dates using the format 'dd/mm/yy', when I return to the dataform, via the macro, I note that the Date Format has been altered to show as 'mm/dd/yy'. It is fine for me, however, for the person who has no experience with DataForms or even Excel it could be quite disconcerting.

The default setting for both systems have been set correctly.

Any clues?

From a command button on a dataform the "on click" event,
can I run code from a Standard module?

Totally, separate Q:
In a standard module
where I use
Dim LrowS2 as Integer
and later in the code I have a line:
LrowS2 = Range("B65356").end(xlUp).row

I get compile error at this point, with explanation of:
Statement Invalid outside Type Block..
???? Not a clue here....

Hi

For some strange reason I cant seem to use the DataForm (Data > Form).

It returns with an error stating it .....cannot determine which row in your list or selection contains column labels.....

Now if I select the same cell and run it manually via Data > Form. It works fine.

Im using the following code to bring access the data form

	VB:
	
ActiveSheet.ShowDataForm 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Anyone come across this before?

Thanks

Hello everybody,

Can anyone help me with this problem? I am trying to sort my data by date the oldest being on top. I right clicked the worksheet I wanted to have sorted and installed this code. (the code is below.) My problem is that I am using a dataform to log my info onto the sheet. The dates will not sort when I complete the logging of info into the data form, unless I unhide the sheet and click on it. What can I do to make the sheet sort after info has been logged without having to open the spreadsheet? The code below works great if you are logging directly onto the sheet.

Any help would be greatly appreciated.


	VB:
	
 
Option Explicit 
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 
    Application.ScreenUpdating = False 
     
    Range("A2:i" & Range("A3").End(xlDown).Row).Sort _ 
    Key1:=Range("A2"), Order1:=xlAscending, _ 
    Key2:=Range("b2"), Order2:=xlAscending, _ 
    Key3:=Range("c2"), Order3:=xlAscending, _ 
    Header:=xlNo 
     
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello everyone

I have a cmdbutton on a sheet which I'm trying to use to open a form I made. For some reason I keep getting the error below every time I click on the button... Please Help me.

Error Message:

Run-Time error '424'

Object Required

If you debug this is the code that it will bring you to:


	VB:
	
 OpenDatayform_Click() 
     
    [COLOR="Yellow"]DataForm.Show[/COLOR] 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please help. Thank you

Hi,

I have a userform that depending on the data in a combobox places other data into textboxes from a sheet. Does anyone know a way of coding it so I can edit the information in a cell? When the user is finished updating the data I would like to be able to overwrite the old data in the worksheet with the new data from the userform.

I'm using the code below to get the data into the textboxes based on the data in the combobox.


	VB:
	
 subname_Change() 
    Dim res As Variant 
    On Error Resume Next 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 2) 
    TextBox1.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 3) 
    TextBox2.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 4) 
    TextBox3.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 5) 
    TextBox4.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 6) 
    TextBox5.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 7) 
    TextBox6.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 8) 
    TextBox7.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 9) 
    TextBox8.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 10) 
    TextBox9.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 11) 
    TextBox10.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 12) 
    TextBox11.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 13) 
    TextBox12.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 14) 
    TextBox13.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 15) 
    TextBox14.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 16) 
    TextBox15.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 17) 
    TextBox16.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 18) 
    TextBox17.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 19) 
    TextBox18.Value = res 
    res = Application.Index(Range("'list'!$A$2:$t$1000"), _ 
    Application.Match(subname.Value, Range( _ 
    "'list'!$A$2:$A$1000"), 0), 20) 
    TextBox19.Value = res 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have this code to send the data, but it's not placing it where it needs to go. It places it in random areas on the list sheet. I need for it to place the data back to the cells that data is coming from.

I know that it would be easier to use a dataform but for this particular one I need it to be on a userform.


	VB:
	
 Sendbutton1_click() 
    Application.ScreenUpdating = False 
    Set c = ActiveCell 
    c.Value = Me.subname.Value 
    c.Offset(0, 2).Value = Me.TextBox1.Value 
    c.Offset(0, 3).Value = Me.TextBox2.Value 
    c.Offset(0, 4).Value = Me.TextBox3.Value 
    c.Offset(0, 5).Value = Me.TextBox4.Value 
    c.Offset(0, 6).Value = Me.TextBox5.Value 
    c.Offset(0, 7).Value = Me.TextBox6.Value 
    c.Offset(0, 8).Value = Me.TextBox7.Value 
    c.Offset(0, 9).Value = Me.TextBox8.Value 
    c.Offset(0, 10).Value = Me.TextBox9.Value 
    c.Offset(0, 11).Value = Me.TextBox10.Value 
    c.Offset(0, 12).Value = Me.TextBox11.Value 
    c.Offset(0, 13).Value = Me.TextBox12.Value 
    c.Offset(0, 14).Value = Me.TextBox13.Value 
    c.Offset(0, 15).Value = Me.TextBox14.Value 
    c.Offset(0, 16).Value = Me.TextBox15.Value 
    c.Offset(0, 17).Value = Me.TextBox16.Value 
    c.Offset(0, 18).Value = Me.TextBox17.Value 
    c.Offset(0, 19).Value = Me.TextBox18.Value 
    c.Offset(0, 20).Value = Me.TextBox19.Value 
    With Me 
        .sendbutton1.Enabled = False 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I hope this is clear enough to understand. Can anyone help? I have also attached a file if anyone didn't understand my explanation.

Thanks,

Larry

I would appreciate some guidance with a problem, I have recorded a macro to select a range of cells and then call the dataform for users to input data. I have attatched the macro to a button on the worksheet. When the macro plays back I get a "run time error 1004" "show dataform method of worksheet class failed". The cells select ok but the form does not show, when I run the debugger it highlights the "ActiveSheet.ShowDataForm" line of the code.
The code in the macro is below,

Sub Receive_Delivery()
'
' Receive_Delivery Macro
' Macro recorded 20/01/2005 by Dan Fox
'

'

Range("U20:AJ328").Select
Range("AJ328").Activate
ActiveSheet.ShowDataForm

Range("B21").Select

End Sub

Just a quick question. Is it possible to set the NumLock as on when loading a dataform in Access please?

Hi Everyone,

I am having a major issue with one of the functions of excel I never knew existed: the data form. Somebody created the spreadsheet I am using before me so I am editing what someone else has already done. They have a spreadsheet set up that holds multiple records about company information (a database you could call it). A data form has also been set up which works nicely to edit records. The issue I am having is that I was asked to insert one column to the database, which I did. Now I cannot figure out how to add this last column to the data form. I tried reselecting all cells, trying to recreate the dataform, but nothing works! Anybody have any ideas for me?

Thank you for any help.

I am not sure if this has been addressed before but how can the screen position of a standard XL Dialogue box be controled via code?

One example is the XL Records entry form whih I would like to always have it displayed on the top right corner of the screen everytime the user evoques it through Data->Form.

Any ideas how to best achieve this ?

I have code that calls a dataform when I click on a command button. Regardless of where I am in the row, the command button is in column A. I want the selected cell to move to column B in the same row so that when the userform displays it will show the correct data. (I have used
Private Sub UserForm_Initialize()

Container.Value = ActiveCell.Value
PONumbers.Value = ActiveCell.Offset(0, -1).Value
SizeType.Value = ActiveCell.Offset(0, 1).Value
Vessel.Value = ActiveCell.Offset(0, 2).Value
'etc
End Sub
to populate my userform.

Can I add something in my call code to also select the cell I want? Or alter the initialization code to start at the beginning of that row? (I don't know how to do that either)

Thanks,
Kristen


No luck finding an answer? You could always try Google.