• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

excel vba worksheet design - 2007

Hi,
I have seen a work sheet design where it appears as a blank page with no cell borders. - just black sheet
where you can put form control etc. something similar to the file attached. - input tab

Also I am trying to learn database driven forms.
Can any please post a helpful tutorial.

Thanks  FormSheet.xls
0
SameerMirza
Asked:
SameerMirza
  • 13
  • 12
2 Solutions
 
SiddharthRoutCommented:
>>>I have seen a work sheet design where it appears as a blank page with no cell borders. - just black sheet
where you can put form control etc. something similar to the file attached. - input tab

For this effect, Simply Click on

Excel 2003

Menu Tools~~> Options: Under View Tab, uncheck "Gridlines"

Excel 2007

View Tab~~> uncheck "Gridlines"

Let me go through your sheet to answer the 2nd question :)

Sid
0
 
SiddharthRoutCommented:
>>>Also I am trying to learn database driven forms.
Can any please post a helpful tutorial.

To begin with. You can have different kind of databases work with Excel. For example Access, SQL, MYSQL or as a matter of fact Excel itself.

In your case it is Excel's Sheet itself which is working as a database.

Here is a brief way on how can you interact with an Excel worksheet to Store Data.

For example if you want to store "Hello World" in Say Sheet "PartsData" in cell A1 then this VBA command will do exactly that.

Sheets("PartsData").Range("A1").Value = "Hello World"

I suggest that if you are not familiar with VBA programming then refer to these few links. They would definitely help :)

After that if you still have question, please feel free to ask and we will definitely help.

http://www.xlpert.com/toc.htm
http://www.vbtutor.net/VBA/vba_tutorial.html

HTH

Sid
0
 
SameerMirzaAuthor Commented:
Thanks alot sid.
Can you please provide me with an example form which is inserting data in access table? an example or a tutorial would be good.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SiddharthRoutCommented:
Sure. However it will taken me quite some time to create a sample for you. I would recommend you to see this link which already has an example :)

Topic:Export data from Excel to Access (ADO) using VBA in Microsoft Excel
Link: http://www.exceltip.com/st/Export_data_from_Excel_to_Access_%28ADO%29_using_VBA_in_Microsoft_Excel/425.html

Give it a try.

Sid
0
 
SameerMirzaAuthor Commented:
thanks again.
I appretiate your help and it sort of tell you how to do it.
But it would be very helpful if we could find a excel example that shows you how to. :/
Problem is I have very little time and need to do alot.
I should already alocate the points to you. this is simply the favour.

Thanks
0
 
SiddharthRoutCommented:
Sammer, it is not about points :)

Ok. Do you have any Access database ready with you?

Sid
0
 
SameerMirzaAuthor Commented:
yes
0
 
SiddharthRoutCommented:
Great. Can you upload the database without any thing in it so that I can use that to create a sample for you. It will save me time to create one from scratch.

Sid
0
 
SameerMirzaAuthor Commented:
Please find the attached db

Thanks
dummy-db.zip
0
 
SiddharthRoutCommented:
Gimme an hour max. It will be done much earlier than that though :)

Sid
0
 
SiddharthRoutCommented:
It doesn't have any tables?

Sid
0
 
SameerMirzaAuthor Commented:
sorry I though you wanted it empty.
Here is another copy with a table called software.

dummy-db.zip
0
 
SiddharthRoutCommented:
>>>sorry I though you wanted it empty.

Yes but I wanted the table structure :)

This also doesn't have a table structure.

Ok leave it. I am creating one from scratch.

Sid
0
 
SameerMirzaAuthor Commented:
sorry :/ you are dealing with a kid
0
 
SameerMirzaAuthor Commented:
But I thank you for all the effort and if you are going to make an example then please do it both ways.
That is import/export.
I am sure I will b able to figure out few thing if I have one working example
0
 
SiddharthRoutCommented:
Ok here is an example. Please extract the folder and open the excel file.

For Exporting

Enter values in the yellow cell in Sheet "Export" and then click on 'Update Database' Button.

Code used for exporting

Private Sub CommandButton1_Click()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Dim DatabaseFile As String
    
    '~~> Path of Database file
    DatabaseFile = ActiveWorkbook.Path & "\dummy db.mdb"
    
    '~~> Connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" & DatabaseFile & ";"
    
    '~~> Open a recordset
    Set rs = New ADODB.Recordset
    
    '~~> Table1 is the name of the table
    rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    With rs
        .AddNew '<~~ create a new record
        '~~> add values to each field in the record
        .Fields("FirstName") = Range("C6").Value
        .Fields("LastName") = Range("D6").Value
        .Fields("Address") = Range("E6").Value
        .Update '<~~ stores the new record
    End With
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
    MsgBox "Data base updated"
End Sub

Open in new window


For Importing

Enter the First Name in the blue cell in Sheet "Import" and click on "Retrieve from Database" button

Code used for exporting

Private Sub CommandButton1_Click()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Dim DatabaseFile As String
    
    '~~> Path of Database file
    DatabaseFile = ActiveWorkbook.Path & "\dummy db.mdb"
    
    '~~> Connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" & DatabaseFile & ";"
    
    '~~> Open a recordset
    Set rs = New ADODB.Recordset
    
    '~~> Table1 is the name of the table
    rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    With rs
        If rs.EOF() Then .MoveFirst
        Do While Not .EOF()
            If .Fields("FirstName") = Range("E1").Value Then
                Range("C4").Value = .Fields("FirstName")
                Range("D4").Value = .Fields("LastName")
                Range("E4").Value = .Fields("Address")
                Exit Do
            End If
        Loop
    End With
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Open in new window


Fields used in database are

FirstName - TEXT
LastName - TEXT
Address - MEMO

Note:

1) This is a very simple example which you will have to amend to suit your needs
2) The macro examples assumes that your VBA project has added a reference to the ADO object library. You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft ActiveX Data Objects x.x Object Library. I have already done that in this Excel File.
3) I have commented the code so that it is easy to understand :)
4) Feel Free to ask if you have any more questions.

HTH

Sid
Sameer.zip
0
 
SameerMirzaAuthor Commented:
Thanks alot.
Adding values seem to work fine.
But I added a record under 'sam'
the program crashes when I try to retrieve 'sam'
No error - excel stop responding
Sam.zip
0
 
SiddharthRoutCommented:
Oops my mistake.

Add this line after "End If" in line 26 of the 2nd code.

.MoveNext

Open in new window


Here is the updated file. Also note that if you have entered "sam" then you will have to look for "sam" and not "SAM" or "Sam".

If you don't want it to be case sensitive then change the line

If .Fields("FirstName") = Range("E1").Value Then

to

If UCASE(.Fields("FirstName")) = UCASE(Range("E1").Value Then)

Open in new window


HTH

Sid
Sameer.xlsm
0
 
SameerMirzaAuthor Commented:
:) thanks
One very last and simple. How do you set the code edit to display line numbers
I have tried view -> couldnt find anything
0
 
SameerMirzaAuthor Commented:
I wish I could alocate more point.
Thanks alot for your help. I guess I am going to calling you whole week. :)
0
 
SiddharthRoutCommented:
You cannot in VBA. At least I am not aware of.

When I mentioned line number above I was referring to the line number which EE displayed in the post :)

Sid
0
 
SameerMirzaAuthor Commented:
Is there a way to request your attention to a question another time?
Or any way I could contact you for help - I will make sure I dont bother you to much
0
 
SameerMirzaAuthor Commented:
Sure I was just wondering if you could. - I have fixed the code according to your instructions and works fine :)
0
 
SiddharthRoutCommented:
>>>Is there a way to request your attention to a question another time?
Or any way I could contact you for help - I will make sure I dont bother you to much

Sure just ask a related question.

Sid
0
 
SameerMirzaAuthor Commented:
thats very helpful.
Here we go then. :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now