i currently have an excel based application with VBA code. I'm trying to add some controls around the app by prompting user for login and password credentials prior to ODBC connection at runtime. So when application is launched, prompt appears, user enters login and password, if correct, it goes on to verify user has ODBC connection at runtime.
View 2 RepliesI am currently querying data from an Oracle database through MS Access and then passing it to Excel for the user to work. Not all users have MS Access so I would like to automate the data pull from Excel
Database information:
The DNS is: a150
The Tables are:
MFE.Items
MFE.Vendors
Sample SQL string would be:
My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?
The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.
Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).
I'm trying to create a dropdown list on my "job master" sheet with reference to cells in an ODBC connection where a position "description" will give me a list of names. Then I want to be able to select another list of names individually with reference to the name I Just selected.
Staff
Description
Foreman
[Code].....
*I was able to do this using lists and indirect list (Personnel Sheet) but it doesn't work with my ODBC connection. I attached the excel file I am using.
Why this sql query doesn't return values? My excel version is 2007.
Code:
if object_id( 'TEMPDB..#TMPDOC') is not null
begin
drop table #TMPDOC
end
create table #TMPDOC (Code varchar(5) NOT NULL)
[Code] ...........
With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.
This is a two part process:
Part one:I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder Where B is equal to "1" And Where C is equal to a changing field under Sheets("Macros").Range("B2")
Here is part one
Code:
Sub Part1()
Sheets("Open PO by Vendor").Select
Sheets("Open PO by Vendor").Cells.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _
, Destination:=Range("$A$1")).QueryTable
[code]...
Part one works perfectly.
Part two is where I have problems.Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.
I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.
As follows:
In particular: *E3 from Part 1*, etc.
Code:
"SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _
, _
"tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _
)
Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.
1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.
2. Create a nested if table and
Change
Code:
WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"
to the value of that nested if table
The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.
3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.
What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.
I have below piece of code which I'm not sure how to finish up. What I'm trying to do is have 'Username' and 'password' entered before logging into the workbook. It does not have to be 'UserForm" if it is possible, but it would be useful if we use Ucase...
I'm just not sure how this code works with
"Sub sUserLogon(strStandardID As String, strPassword As String)"
stated in this way..
VB:
Sub sUserLogon(strStandardID As String, strPassword As String)
Dim strUserName As String
Dim StrMessage As String
Application.DisplayAlertsAlerts = False
StrMessage = "User Logged In"
[Code] ......
I want the userform to display an input box asking the user to enter their 'login ID'. This has to be either "SUPERVISOR" or "STAFF".
Upon correct entry i need it to then display another input box asking for a 'password'. This needs to be "SHOES" for both logins.
If the login is not equal to SUPERVISOR or STAFF I need a message box displaying "username incorrect". and for it then to show the input box requesting the 'login ID' again.
Again if the password is not equal to "SHOES" to display a message box displaying "password incorrect" Then show the input box requesting the password.
Upon correct entry for both the username and password i would like my other userform to show. This is displayed by:
Load UserForm1
UserForm1.Show
I would like the correct 'username' to be displayed in cell "D27" on the active sheet and 'password' to be displayed in cell "F27" also on the active sheet.
If there is a way to display the password using asterix's for security that would be useful.
I found some username and password login code that I am editing for my needs, but I am having some trouble with it. I keep getting run time error '448': Named argument not found on the following line:
MatchCase:=False, SearchFormat:=False)
[Code] .....
I have also attached my workbook.
Equipment Return Log.xlsm
I have come up with the idea to create a userform to act as a login page for my project. I have the project complete and need a way to protect it. I want the userform to display an input box asking the user to enter their 'login ID'. This has to be either "SUPERVISOR" or "STAFF". Or If I have a table of 'users' and 'passwords' in sheet (USERS) to match them against that if it is easier?
Upon correct entry i need it to then display another input box asking for a 'password'. This needs to be "SHOES" for both logins. If the login is not equal to SUPERVISOR or STAFF I need a message box displaying "username incorrect". and for it then to show the input box requesting the 'login ID' again. Again if the password is not equal to "SHOES" to display a message box displaying "password incorrect" Then show the input box requesting the password. Upon correct entry for both the username and password i would like my other userform to show. This is displayed by:
Load UserForm1
UserForm1.Show
I would like the correct 'username' to be displayed in cell "D27" on the active sheet and 'password' to be displayed in cell "F27" also on the active sheet To show who is 'Logged On'. If there is a way to display the password using asterix's for security that would be useful.
I have a workbook which, on opening, shows a logon form and depending on the username and password entered only certain sheets open - there are about 10 usernames and passwords - i have only included 2 to keep the code a bit shorter. I got the code below from here and it works perfectly but I want to make it a bit easier to add new usernames and passwords and am thinking of using a Vlookup table. The code i currently use is:
Option Explicit
Const sName1 As String = "Lisa"
Const sName2 As String = "Philip"
Const sPw1 As String = "update"
Dim sPW As String, sUser As String
Dim sMsg As String, sTitle As String, sStyle As String
Dim iCounta As Integer
Private Sub cmbValidate_Click()
sTitle = "Incorrect Password"...........
I am trying to create a log-in page on a userform, frmlogin in "sheet3." The userform is simply a textbox, Username (txtuser) and a textbox, Password (txtpass). Command Button is cmdcheck
I want the User to enter their username and password and for my macro to verify that they match referencing data in a seperate worksheet, (Sheet6).
Upon verification I want the user to be unloaded on "sheet1"
I am currently getting this message: "unable to get vlookup property of the worksheetfunction class"
VB:
Private Sub cmdcheck_Click()
Dim Username As String
Username = txtuser.Text
[Code]....
I have a userform that numerous people need to fill in. I want to add a login function so that each person has their own login detail. Their will be a command button called login. Until they login in they cannot enter any data.
View 4 Replies View RelatedI need a macro ie when a user opens a macro enabled workbook,he should be asked for a username and password to access workbook. Administrator should have right to create users with reset password rights
View 5 Replies View RelatedIm using the following code on a User login userform. This code is for the Login command button to access the workbook (the user clicks on it after entering there username and password).
For some reason if the password is numbers only it just returns message box "Incorrect password, try again."
As soon as I put a single letter into the password it works fine,
[Code]....
Using Excel2010 in Windows7. I have a UserForm
1: TextBox for Input Username (txtUsernameIn)
2: TextBox for Input Password (txtPasswordIn)
3) Submit Button (cmdSubmit1)
I have a Worksheet
ColA: Username
ColB: Password
And this entire Range A:B, I have named "UserRegister"
I have code, attached to the cmdSubmit1 button on my UserForm. The purpose is to allow access to a veryhidden Worksheet "ADMIN".
The code 'works' if I enter in both the correct UserName and Password (ie: the code completes & Admin sheet opens up).
The code 'works' if I enter in the correct UserName and the wrong password (ie: code completes it's run, my msg pops up to say there is wrong input, unloads the form & sheet ADMIN stays veryhidden).
However, if I enter the wrong username (with either right or wrong password) the code breaks and I get a debug error. I know my code is obviously wrong/missing something etc - but I can't work it out! The line of code highlighted on the break, is between the *** below
VB:
Private Sub cmdSubmit1_Click()
Dim Username As String
Username = txtUserNameIn.Text
Dim password As String
password = txtPasswordIn.Text
[Code] ......
How The Vba Code Used For
My Excel File Name "eai.xls" ,when I Click The File , It Open The Sheet Name "menu" With The Inputbox Prompt:="put Your Id"
When The User Put The True Id , It Will Show The Input Box Prompt:="your Password" . If The Password True Will Activate The Sheet Name "data" .and Hide All The Menu Bar.
If The Wrong Id Name Show The Msg.box "re Enter Your True Id " ,that Also For The Wrong Password In Which If User Put More Than 3 Times Wrong , Will Close The Workbook And Exit File.
The User Persons Allow 10 Person Id . Keep In The Table Of The Sheet Name " Id And Password" The Id No. Are In The A1:a10
Password No. Are In B1:b10
This Sheet Name Is Hide And Protect
The VBA password prompt keeps popping up when I closed my workbook even though I haven't opened up the VBA window. How do I stop this?
View 1 Replies View RelatedI need to put together a macro, which will be assigned to a command button, that will prompt the user for a pre-specified password, and, if the password is correct, return a pre-specified value in a different cell. Is there any way to do this?
To put it in perspective, at my company we have approval sheets that need to be signed by several executives that are scattered all over the building. The first executive gets the copy, signs it, sends it off, etc. They tend to dissappear on occasion, so we are looking to make it all electronic, where they can access the file in a shared folder, and the if they enter their password correctly, their initials are input into the specified cell. They can than save the file for the next executive to do the same.
I have a macro that locks the cell (cell rangeis given) after entering data into cell & thereafter if we have tochange the value of cell it asks for password. It works well.
I want somechanges in this macro - currently if I move cursor on locked cell then alsoit prompt for password & i want if i edit that cell by double click or F2then only he ask for password.
I am trying to complete a toolbar that will lock and unlock all the sheets in a workbook. While I can set the password automatically I'd like to have the macro ask for the password instead of hardcoding it.
Here is the code I currently have and works so long as I have already set the password in the workbook to match what is in the macro
I have created a workbook that mostly works with VBA codes. Within the code I have ensured that certain worksheets remain hidden. However, I have also written a macro to access what I called the ADMIN worksheet, which has several commands enabling updates and troubleshooting. Long story short, I would ideally like to prevent users from accessing that worksheet. I have already created a prompt (Msgbox) asking the user if he/she wishes to proceed with the macro but I would prefer being able to have a password prompt to run the macro.
View 4 Replies View RelatedI am trying to password protect user input on a specific text entry, for example if a user tries to enter the letter A they are prompted they require a password (I am trying to do this for authorisation.).
View 3 Replies View RelatedI have created an Excel sheet that retrieves data from a ODBC source. I have created a macro using macro recorder to refresh the data by re-connecting to the database. However, the connection requires a password prompt and when the macro is run, you are still required to enter the password.
Is there anyway to make the macro so that the password is automatically entered and the user will just have to press a button to fully run the query update?
have a password protected spreadsheet with a userform for people to enter data. This allows them to enter data, but stops them from editing or deleteing data once entered.
When the spreadsheet is opened, it asks for a password to unlock it. Hitting cancel allows the user to continue and the userform pops up for the user to enter data. I would like to disable the initial password prompt when the file is opened to avoid confusion on the users part.
Macros are just not my strong suit. I can usually find my answers here but this one has me stumped. I have rewritten this thing everywhich way but loose and this is my latest version.
Sub SetProtect()
'
' protect Macro
' prompts for password protection
'
' Keyboard Shortcut: Ctrl+p
'
Dim Response As String
Dim Answer As Integer
SetProtect:
Response = InputBox("Enter password")
If Response = "safety2" Then.......
When I close one excel document, I get prompted for a password.. And it won't close (I don't know the password) The only way I can get it away is by terminating it in task manager. I have read alot of threads in here, where the solutions should be to uninstall Google Desktop..
View 5 Replies View RelatedI've got an Excel document I've built which uses multiple ODBC QueryTables
View 3 Replies View Relatedhow to do this with Java but not VBA, I have the capability of understanding it I just need to know where to look because I can't seem to find any solid information on the net from my searches. Does anyone have any websites, other threads or anything they could direct me to for this kind of thing.
I was contemplating putting this in the Access thread but I want to connect to Access through Excel so I think it fits both.
My plan is to use a macro to send specific cells to five seperate tables in an Access database using VBA and SQL.
I would like an ODBC query to return the reults to a textbox on my userform.
View 1 Replies View Related