copy VBA form excell to access 2007

ahc59always · Apr 8, 2013

Hello forumfriends,

I’ve got the following question,
I have a Excell spreadsheet named “MEMnrs” within that spreadsheet is a worksheet named “10418”
In that worksheet is written a VBA code that I would like to copy to my Database.

In my DB is a form named “Informatie” and in that form is a button called “copyVBAfromExcell”
What I would like to do is copy the VBA code from the excel worksheet into the VBA editor in Access 2007 behind the button with a on-click event.
I did read something about late and early binding of an object, but this was very difficult for me.

Imagine,
In the Excell spreadsheet is written: VBA code,

Private Sub copyVBAfromExcell Click()
Dim MyInput As Date

myInput = InputBox("Give here the date.")
If myInput = "15-04-2013" Then
MsgBox ("This is the correct date, your membership will continue")
Else
MsgBox ("This is the wrong date, try again.")
End If

End Sub

All help and advice is welcome
The reason why I’am asking is; in my spreadsheet is information that some people don’t have to see.
If you have a solution or otherwise, please make it as simple as possible.
Friendly regards, ahc59

SydneyGeek · Apr 8, 2013

I am assuming from this that you want to control the use of a database that people trial or buy. Is that correct?

If so, consider splitting the database so that you have front end logic and back end data. It's best practice for deploying an Access DB.
You should also compile the front end into an .accde version. This protects all the code in your forms and reports, and prevents anyone from changing their design. Just make sure you keep a full version for yourself, so you can continue devleopment!

If my assumption is correct you will have no need to automate writing VBA to the IDE. The code will work in the database but will not be viewable.

If you really need to write code to the IDE, check out Chip Pearson's site. It is Excel-based but the process is the same.

Denis

ahc59always · Apr 9, 2013

Hoi Denis, (master)

Your assumptions are correct, but I really need to write code to the IDE?. Can you help point me out Chip Pearson's site, do you have a link for me?
Thanks in advance,
gr.ahc59

SydneyGeek · Apr 9, 2013

See this page... Programming In The VBA Editor

Denis

ahc59always · Apr 9, 2013

Hey Denis,
I'am just an old man of 53, and it is 09:12 in the morning. I'am sorry but I dont see anything of your suggestion on this page.

Norie · Apr 9, 2013

Why don't you just copy and paste the code manually?

ahc59always · Apr 10, 2013

Hi Norie,
Your question is correct, but it in real a very large code, not the exmaple a mentioned earlier. I thougt when I have an automatic code to to this job for me,(copy and paste)
I could be sure of myself to make no mistakes. The range of the code in excel is A1:D264. Ill need this code frequently, so hence my question.

SydneyGeek · Apr 10, 2013

It's still much easier to copy and paste the code across than to write directly to the IDE.
The code I pointed you at was a bunch of techniques and settings that are required to edit the IDE in code. Building a solution is a large piece of work on top of that. You may find a post in this forum or elsewhere, showing a complete routine, but it's not common.
Is it the database or the spreadsheet that you are trying to protect?
If it's the database, the .accde method is much simpler again. The code won't be readable. And it's widely used so you can get help from numerous sources.

Denis

Norie · Apr 10, 2013

Have you considered the security implications of writing code with code?

For it to work the user would have to set their security levels to allow programmatic access to the VBA Project.

That isn't always desirable.

ahc59always · Apr 10, 2013

Hi Denis,

I'am agree with you to use the copy and paste method, but when I use this method like I told before, I'am afraid to make mistakes. My simple thougts was when I copy the VBA
code, what is a large range of cells in excell with a function or sub within access 2007, Ill be avoiding any mistakes of copying the ranges. Ill believe you offcourse when you say, to make this code is very much work for techniques and settings, while I was thinking of a short code to do so. (five or six lines perhaps to write the code). Ill accept your view on this topic, because your the expert in this.
Many thanks for you and Norie for the good advice!
Greetings, ahc59

copy VBA form excell to access 2007

ahc59always

New Member

Excel Facts

SydneyGeek

MrExcel MVP

ahc59always

New Member

SydneyGeek

MrExcel MVP

ahc59always

New Member

Norie

Well-known Member

ahc59always

New Member

SydneyGeek

MrExcel MVP

Norie

Well-known Member

ahc59always

New Member

Similar threads

Forum statistics

Share this page

copy VBA form excell to access 2007

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

Well-known Member

New Member

MrExcel MVP

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock