I would like to unlock the work sheet based on the username. However this code does not seems to be working.
The object is that only these users have access to the locked cells on a sheet and those users not on the list would only have access to the unlocked cells.
I have the following VBA in my spreadsheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect ("kellyz")
If Intersect(Target, Columns("o:o")) Is Nothing Then Exit Sub
For Each C In Intersect(Target, Columns("O:O"))
[Code] ......
As soon as i type anything in the columns, my sheet is unprotected. I need the sheet protected so that the users can not change my formulas.
I have a thousand names on a column, and I have a several worksheet tabs as locations, such as CA, AZ, TX, and NY.
All names goes to main worksheet, show like this
apitchford 10 100 123
bkishpaugh 9 211 123
blee 14 234 111
cbonny 21 125 412
I need to pull a specific name, example, name = blee, move that name with all data on that row into TX.
I will need to set as automatically, becuase the names add and delete on the main worksheet, and will auto update those tab worksheets. (make sense?)
I have tried INDEX and MATCH, but they keep putting one data in, not all data.
I have created a form that allows users to input data to an excel sheet, and also will enable users to view the data once they are done.
Is there a way to unprotect the sheet using VBA so that the data can be written, and then protecting once the data has been input?
I currently have set up two macros that will unprotect and protect all sheets. When I run the macro I noticed that it will not unprotect the sheets that have been set as a new sheet for the chart (the chart is not a object in a sheet it is a sheet all by itself) Can anyone tell me where I have gone wrong?
View 14 Replies View RelatedI cannot get my spellcheck to check any textbox. Is there a way to do this? Unprotecting sheets before spellcheck will work, but nothing about texboxes.
View 7 Replies View RelatedI want to run a macro that puts information from one sheet into another. The destination sheet is protected so I want to unprotect the sheet, do the copy and pasting then reprotect the sheet. The sheet has a password and I can't work out how to put that into the macro. It works fine without a password.
View 9 Replies View Relatedi would like the user to be able to unprotect the workbook via clicking a button. this button would first ask the user for a password before unprotecting the workbook.
how can i code this through VBA?
i already built the following VBA ...
I have a workbook which contains 52 worksheets, all of which need protecting. Is there a way of protecting / unprotecting all these worksheets without having to enter my password on each separate sheet?
View 3 Replies View Relatedscenario: sheet & work book are protected to users; however need them to sometimes insert a comment to this sheet.
all columns/rows are locked except columns K, R, Y and every 7th column thru CJ (rows are from 4 - 100).
how do I enable a user to insert a comment without unprotecting sheet / WB? note: there's no pattern, it could be any cell within aforementioned columns.
I've got code that loops through approximately 700 excel files and unprotects the workbook. I have to run this code for five consecutive days, and the issue I'm having is for the files that are unprotected on Day 1, the code still runs for those files the next 4 days.
There may be only 10-15 new files submitted on day 5, but the code still runs through all 700.
I can get it working standalone, and when I protect it, but when I share the document it errors on me and I dont know why. I know there are limitations, but this macro should still run (the macro should unprotect everything, then protect and share after it has run).
Public Sub RQV()
Dim lngRow As Long
If MsgBox("Do you wish to move RQV data to yesterday? Note that all amendments will be reset also.", vbYesNoCancel) = vbYes Then
ActiveWorkbook.UnprotectSharing
worksheet1.Unprotect Password:="unsecureme"
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
. ScreenUpdating = False
End With...................................
I have Timesheet workbooks with 3 levels of access (user, viewer & me as developer).
I want use the GetuserName API function (with If/Then) to auto open the wkbk if I am loged in under my network login so psuecode looks bit like this
If network user is me then open wkbk
else ask for username & password
I have googled & searched the forum & the closest I have is this
Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next...............
I've got a sheet with a "Last Edited By:" field and I want this to be populated with the current users name i.e. I suppose I mean the user name that is assigned to Excel, as opposed to the XP log in... although I guess either would do. I'm OK on the change event thing, but just dont know the function to find the the current users name.
View 3 Replies View RelatedI'm trying to use the code listed below on the works shared drive. When I open up the worksheet and save it, my log on identity is left as required in Cell B2 etc.. When someone else uses the sheet it just leaves the company name in this cell and not their log on identity? The date & time function always works ok. Can anyone advise me if what I'm after is achievable.
The code I've used below was used from a previous string on a similar question.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Now()
Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Application.UserName
End Sub[/b]
I have copied a macro off another board that get the username of the computer where the excel file is opened. It works fine on my pc but when I have sent the workbook to a colleague my username came up when he opened it. Also what I am trying to do is to modify the macro that does a vlookup on a range in worksheet 1 from where it picks name and surname (and transpose it onto worksheet 2 on cell A2), email (on B2), tel number (on C2) etc etc.
View 3 Replies View RelatedI 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] ......
This time stamp macro is great but I would also like to record the person who said 'Y' in the same cell as the date stamp '12:00p.m. MWatson'. The persons name should come from the Username.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range([P13], Cells(Rows.Count, "p"))) Is Nothing Then 'change your range on this line"
With ActiveSheet
If Target.Value = "Y" Then
Target.Offset(0, 1).Value = Now
[Code] ........
I would like the name of the User to print in the Footer in Excel.
View 9 Replies View RelatedI am using the below code to access a website with IE.
My next step (of many) is to Login to this website.
The login screen requires that I enter:
User Name:
Password:
How can I tell IE to enter my user name and Password?
Sub Macro2()
'
'Macro2 Macro
' Macro recorded 2/6/2007 by dsggodwin
Public Sub Goto_ToyotaSupplier_Click()
Dim EXP
Set EXP = CreateObject("InternetExplorer.application")
EXP.Visible = True
'put the webpage here
EXP.Navigate ("https://www.portal.toyotasupplier.com/skpi/SkpiGatewayServlet?jadeAction=NCPARTS_SEARCH")
End Sub
I am trying to get the username from the system to display in A1 in a file in Excel. I got this code form internet and put it in the sheet by clicking right mouse on the sheet1 tab and view code and then pasted it there.
Function UserNameWindows() As String
UserNameWindows = Environ("USRNAME")
End Function
And then as per the instructions on the same page I inserted this formula in A1 : =UserNameWindows()
The person stated that by doing this, I should be able to get user name displayed in A1. BUt all it gives me is #NAME?.
i need a macro that will only open a file to certain users using the "get username" environ thing,
and only allow a list of users to access this file,
this will be a log of things that only managers whouls have access to, i have all the managers usernames (windows log ons) and what i need is that if the log on on the current machine, (the one currently opening the file) does not match one on th elist then the file should not open.
i could pasword protect the file but since my managers are kinda lazy and i wnat to show off a little (maybe hopefully that will push my promotion forward a couple of days at least)
I'm trying to create a program that will automatically log in to a website that requires a username & password. I am able to enter the username and password in the correct text boxes, but I can not get the form to submit to approve the login. It refreshes and removes the password.
I have attached my code. The web url is inside it.
Sub IE_login()
Dim ie As InternetExplorer
Dim C
Dim ULogin As Boolean, ieForm
Dim MyPass As String, MyLogin As String
Set ie = New InternetExplorer
ie.Visible = True
ie.Navigate "https://applications.dacgroup.com/login.aspx"
'Loop until ie page is fully loaded
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
I have a problem with the Excel UserName & opening of a second xls File. Info:
The first file macro, looks as to the Excel UserName, and sets which worksheets from a second file in the network the person can see. then changes the Excel UserName to "helpdl", this is done because only 4 people have access to the Workbook on a network drive, see Code below (only the interesting part of the macro)
'Set the Excel UserName to "helpdl"
'(from Sub Change_UserName)
MyName = Application.UserName
OName = MyName
NewName = "HelpDL"
UserName = NewName
'Set the name of the Active File To "Slave"
Set wbSlave = ActiveWorkbook
'Open the Master from the Network (Wiesbaden server sv030100)
Workbooks.Open "\sv030100GruppenDL60116_MEWA Car Policy_Neukonzeption.xls"
'Set the name of the Active File to "Master"
Set wbMaster = ActiveWorkbook...........................
code below, I need :
1) navigate to a web page
2) submit my userId and password
3) beeing redirected to a different url I have to menage this situation for passing url to
4) quering sub
I am using Excel 2000 with win98
Error 429 Can't create object:which one?
Public Sub First()
Dim obEx As Object
Dim obj As New MSHTML.HTMLBody
Dim strPass As String
Dim elUid As New MSHTML.HTMLInputElement
Dim elPass As New MSHTML.HTMLInputElement
Dim elSubmit As New MSHTML.HTMLInputElement
Dim strUid As String
Dim Desc As String
I have an Excel macro that pulls an external .txt file from a particular directory on a users computer. The directory is different for each user because of the way the computers are configured, see example below.
Bob's computer:
c:obwork
Jill's computer:
c:jillwork
There is a system variable called %username% on each computer that can be used for navigation in an OS enviroment. So if you were at the command prompt on user's computer and typed cd\%username%work you would be put in the desired directory. Is there a way to leverage this system level variable, %username%, in an Excel macro?
Usually when you try to open a file that someone else is using, it lets you know the file is in use, and lets you know who is using it. However, the situation I have is that when a user tries to open the file that is in use, it tells them that the file is in use, but gives them the wrong user who is using it!
For example, User1 opens the file and closes it again. User2 comes along and opens the file and stays in the file. User3 comes along and tries to open the file, but instead of saying User2 is using the file. It says User1 is using!
The file we have was originally built in Excel 97, but we using it through compatibility mode in Office 2007. The file is needed by various users, but putting it on shared access is not an option.
I have a list of tweets, and I would like to extract just the username found within the tweet. I'm not sure if a formula is the best way to do this, or possibly a macro.
The example I've attached is only of 18 tweets, but the spreadsheet I'm looking to use this on is 100,000+ tweets, so I need something easily scalable. One thing to note is that a tweet can have more than oneuser mentioned in it, such as the first tweet in the example.
Tweet Example.xlsx
Excel can automatically recognise individuals by their windows login name so you can fully automate the protect/unprotect of the correct sheets without even individuals having to use passwords.
The first step though would be to gather the id's of all users.
1. Create an additional sheet and call it UserLog
2. Put the following code into all the worksheet code modules
[Code] ......
Once you know the windows login names and which sheet they are responsible for you can add the following code to each worksheet.
[Code] .....
I have tried as per below and it is not working.
I have about 50 user that uses one document and will record their comments to instructions, but only 20 users will be able to add the instructions. The document needs to autolock when it is saved and can only be unlock with the username of the 20 users.
I'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:
[Code] ......
Why the above code does not respect the Master User "Jane" should be able to see all sheets?
Original source for this code was found here:
HTML Code: [URL]....