Excel 2010 vba question?

Question

I have a function that grabs the current user name of the user logged in, how can i pass this to the path in the 2nd function?

Thanks for any and all help!


Public Function Getusrname()
usrname = Environ("USERNAME")
End Function

Public Function WriteRecord()
Open "C:\Users\%usrname%\Desktop\MyFile.txt" For Append As #1
Print #1, record
Close #1
End Function

Answer 1

AJ probably has it right, but depending on the version of VB you are using you can return a value like this:



Public Function Getusrname()

Getusrname = Environ("USERNAME")

End Function



Public Function WriteRecord()

dim strUser as string, strFile as string

strUser = Getusrname()

strFile = "C:\Users\" + strUser + "\Desktop\MyFile.txt"

Open strFile For Append As #1

Print #1, record

Close #1

End Function

Answer 2

1

Answer 3

Public Function Getusrname()

return Environ("USERNAME")

End Function



Public Function WriteRecord()

username = Geusrname()

Open "C:\Users\%usrname%\Desktop\MyFile.txt" For Append As #1

Print #1, record

Close #1

End Function

Answer 4

Actually in some ways I like portions of your code better than Garbo's, but Garbo is pretty good at VBA. Find tends to work faster than looping through the UsedRange like Garbo is doing, especially when if you are going through multiple worksheets. However I see several errors with your code and a few I think Garbo caught. First, I'd totally get rid of the line: TextBox1.Value = "*" If you have that line in there, all your macro will ever search for is an asterisk. It won't be searching for what your user typed in Textbox1. Secondly, you didn't declare the variable Wks as a Worksheet. I'd make sure to declare it as a worksheet or Excel may not know that you are looking through each worksheet in the worksheets collection. You declare it like so: Dim Wks As Worksheet It should go right under the start of the macro, right under the sub line that starts the macro. Next is your Find code line. You need to replace What:="*" with What:=TextBox1.Value If you don't do that, then your macro won't search for what the user has entered in TextBox1. Also on that same code line I'd change xlFormulas to xlValues. When you have xlFormulas that looks through the formulas which is basically the formula that is in the formula bar for each cell. That may or may not be the value (what actually displays in the cell). The way you have your code right now it'd likely grab the first cell that has a multiplication formula in it. The next problem you have is that your macro isn't actually looping. Every time you create a For/Next loop you need a Next statement at the end of the loop. You need: "Next Wks" in your code so you can loop through each worksheet. And you need an Exit For statement to Exit the For/Next loop if your search finds something so it doesn't loop through all of the worksheets where it may find nothing there. Here's how I'd write your code: Private Sub OKbutton_Click() Dim Wks As Worksheet Dim MgFound As Range 'Turns the display alerts off such as if Find finds nothing Application.DisplayAlerts = False 'Sometimes Find searches return an error if nothing is found 'so this lets it continue if nothing is found On Error Resume Next For Each Wks In Worksheets Set MgFound = Wks.Cells.Find(What:=TextBox1.Value, _ After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) 'Exit loop if text is found If Not MgFound Is Nothing Then Exit For Next Wks 'Turn the alerts back on now that 'you've done your find searches Application.DisplayAlerts = True If MgFound Is Nothing Then MsgBox "Not Found" Else 'Sets TextBox2 to the value of C6 TextBox2.Value = MgFound.Parent.Range("C6").Value End If 'Turn normal error handling back on On Error GoTo 0 End Sub Now you may want to change xlPart to xlWhole if you want an exact match of the cell's value. When you use xlPart if looks to see if that value is part of any cell's text. So if they type in "the" in TextBox1, then it could return cells that contain "then", "them", "their", "weather", etc. If xlWhole is used it would only return a cell that contains exactly "the". Then if it has to match the case you can turn MatchCase to true instead of its current value of False.

Answer 5

This may be related to what you might be doing:

http://support.microsoft.com/kb/310399



File auditing is built into Windows and the NTFS filesystem (what Windows mostly uses). This would work very well if the file is kept on a server and the other users have actual Windows/Active Directory accounts. When auditing is enabled, use Event Viewer (eventvwr.msc) on the system keeping the file to check auditing entries. If you are an administrator, you can use your own Event Viewer to remotely check the logs on other computers in your network.



Since Vista and Win7 Home Premium don't have access to the Group Policy editor, you wouldn't be able to use this in a non-networked environment. (XP Home technically does, although it is locked out when in Normal mode.)