Command Button in Excel 1

[jdwm2310]

Hi,

I am not too familiar with VB in Excel, I would like to create a command button that when click it would add a new record in the bottom of the file. Can't anyone help me with this..thanks

 

[DaleWatson123321]

Hi jdwm,

Here's a routine that includes a check for one field that is considered a "mandatory" entry field.

The example file I created also includes the use of both a "Data Validation" message, and Conditional Formatting to highlight the "mandatory" cell - IF the user attempts to transfer the data to the database without having entered data into the mandatory-entry cell.

I have a SEPARATE sheet set up for Data Input, and the current entry orientation is VERTICAL.

Formulas on another sheet reference the input cells. These other formulas convert the data from VERTICAL orientation to HORIZONTAL orientation - necessary for transfer to the database.

Sub Transfer_Data() 'transfers data to database
If Range("inp_1&quot.Value = "" Then
Range("chek2&quot.Value = 1
Application.Goto Reference:="inp_1"
Exit Sub
End If
Application.ScreenUpdating = False
Worksheets("Database&quot.Select
LastCell = [A65536].End(xlUp).Offset(1, 0).Address
Range(LastCell).Select
Range("inp_data&quot.Copy
ActiveSheet.Paste
Worksheets("Input&quot.Select
Application.Goto Reference:="inp_1"
Range("inp&quot.ClearContents
Range("chek2&quot.ClearContents
Application.ScreenUpdating = True
End Sub

I hope this helps. Please advise as to how you make out.

If you feel it would help to have the file, just email me, and I'll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[jdwm2310]

Dale,

I send you an email in regards to the file. Thanks!!!

 

[jdwm2310]

Dale,

I am so confuse I don't even know how to begin looking at this. I don't know how to create query using Microsoft Query. Do I need to do this?

 

[DaleWatson123321]

The example file I sent you was based on adding a new record to the bottom of a file within your Excel workbook.

If indeed your file is in another non-Excel file (Access for example) then you'll need to specify that, and specifically ask for assistance from experts with experience in using Microsoft Query.

On the other hand, if your intention is to create a database file "within" Excel - i.e. on another sheet within the same Excel file, then the example file I sent will be of use.

Because I didn't have your actual file to work with, all I could do was to provide you with a "generic" example. The intention was that you would modify this example to fit your exact requirements.

On the "Input" sheet, for example, I have one column entitled "Description" - under which I have "Field_1", "Field_2" etc to "Field_8". You would need to change each of these "Field" names to the actual field names required in your database.

The second column in the "Input" sheet, entitled "Input Column", is where you would have your user enter the data.

You'll notice that I intentionally left the first input cell blank (opposite Field_1). This was to demonstrate what would happen for a "required" field if the user left it blank. In this case, when the user clicks the "Transfer Data to Database" button, the data would NOT be transferred, and that "required" field would be colored RED.

Once the user enters data into that cell, the color disappears. Then clicking the "Transfer" button will result in the data being transferred to the next sheet (named "Database&quot and appended to the bottom of the existing data. The "Input Column" is also blanked out.

If you do intend to use Excel for your database, then I can provide further help.

The BEST approach would be for you to email me your requirements. If you already have a file with the field names and the type of data required, send that file. I'll then incorporate the file I just sent, and return the file to you.

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[jdwm2310]

Dale,

I send you my file and I hope you can incorporate your method with my file. I appreciate everything you have done thus far...thank you very much...

 

[jdwm2310]

Dale,

You are a true life saver...One more question is it possible to allow the user to enter more than one row of information??

 

[DaleWatson123321]

Hi jdwm,

Short answer: Yes.

Let me know how many rows you want to set up in your input range.

If you've made further changes to your file, perhaps you'll want to email me the current version ?

I'll then make the necessary changes.

But this afternoon is NOT a good time for me, as I have another commitment.

I likely won't be able to get to the modification until Monday.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[jdwm2310]

Dale,

I send you the current file..thanks