Re: Auto Refresh Active Worksheet
Re: Auto Refresh Active Worksheet
I am trying to continually refresh a spreadsheet (it is a countdown timer), and i am looking for a way to do this other than hold "F9".
It is excel 97.
Any help would be awesome!
Thanks in advance
------------edit------------- I have included the spreadsheet
Re: April Fools Fun
Thanks very much!
Here's a little joke bit of code I made for a friend of mine who takes things a little seriously at work!
Just run it and wait... :spin:
(To make it a little more authentic you might want to change some of the labels on the userform. Or if anyone has anyway of detecting these automatically lemme know and I'll update the post!)
Click HERE as it is 400ish KB and is too large to upload to this forum.
I have tweaked the code a little, I will add the updated section of code once I get home tonight... it now correctly shows the drive letter of your systems main hard disk and also the total capacity in the correct fields to (almost) correctly emulate the format screen!!
This replaces every bit of code in userform1...
Private Sub CommandButton2_Click() MsgBox "Canceling part way through formatting could cause irretrevable data loss" _ & vbCrLf _ & vbCrLf & "Continue?", vbExclamation + vbOKCancel, "Warning" CommandButton2.Enabled = False CommandButton2.Caption = "Pausing..." UserForm1.Caption = "PAUSING... Format Local Disk " & environ("systemdrive") Application.OnTime Now + TimeValue("00:00:10"), "stage3" End Sub Private Sub UserForm_Initialize() drivename = environ("systemdrive") Set fso = CreateObject("Scripting.FileSystemObject") Set maindrive = fso.GetDrive(drivename) ComboBox1.Value = ((maindrive.TotalSize / 1024) / 1024) / 1024 & " GB" ComboBox2.Value = maindrive.FileSystem TextBox1.Value = environ("systemdrive") & "\\" & environ("computername") UserForm1.Caption = "Format Local Disk " & environ("homedrive") End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 'Prevent anyone from closing the userform with the Close box (x) in the title bar. If CloseMode <> 1 Then Cancel = 1 MsgBox "Please wait for Format to complete", vbExclamation + vbOKOnly, "Warning" End Sub
Re: Open Workbook Window On Top Of UserForm
David, I used yours in the end, but it still looked a little untidy, so rather than bringing up the file I just built a form so that the user can enter the details and it will copy to that spreadsheet automatically!
Nice simple one hopefuly,
I have searched to no avail, so I will ask you lovely guys here!
Using the Web Browser object in VBA, how do I set the home page etc...
If anyone has some sort of resource for some settings I would be so grateful!
Thanks in advance!
Tim[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Found it!
Re: Opening Workbook In New Window From Userform
Thanks for the quick reply!
Ok, I understand how to hide the userform on loading the new workbook. What would the code look like for showing it again on closing of the new workbook?
Also where would this go? Would it have to go in the Workbook_Deactivate part of the newly opened workbook? Because if so, that would cause problems with people opening the file directly from explorer (ie. not from my userform) and then closing when they are done with it.
I have a userform with several buttons on, each doing seperate tasks, three of the buttons however are used to load a workbook stored elsewhere in our central data storage drive.
Loading these workbooks is not the problem, the problem is when loading these they load up behind the userform and not in a new window.
Could someone help me with this one please?
Thanks in advance,
Just a little silly one that I can't fathom, be it that I'm tired or just plain stupid :wink:
All i want is a way, on clicking a button on my userform, where i can bring up a quote randomly picked from a list of quotes and sayings which I will input.
Any help would be appreciated!!
Thanks in advance!
Re: Export Data To Seperate Workbook
Thankyou so much!
I have built a simple little logger for logging insurance policies set up without permission, at the moment it logs all data in sheet2 of the active workbook.
What I want it to do is when submit is clicked is to export and save the data to a seperate (central) workbook to collate all entries and not to save it in the active workbook.
I've inlcluded the file and the code is as follows;
Private Sub CommandButton1_Click() Dim ws As Worksheet, lRow As Long, Str As String Set ws = Sheets("Sheet2") lRow = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row '____error handling______ If TextBox1.Value = "" Or NullString Then MsgBox "Ooops, it seems you haven't entered a policy number!", vbCritical, "Error..." GoTo error1 End If '____error handling end______ If MsgBox("Are you sure you wish to add " & TextBox1.Value & " to the log?", vbYesNo, "Confirm add") = vbYes Then ws.Cells(lRow, "B") = TextBox1.Value ws.Cells(lRow, "C") = TextBox2.Value ws.Cells(lRow, "A") = Date ws.Cells(lRow, "D") = Environ("Username") MsgBox TextBox1.Value & " has been added to the log!" TextBox1 = "" TextBox2 = "" Else End If error1: End Sub Private Sub CommandButton2_Click() TextBox1 = "" TextBox2 = "" End Sub
Re: Display Username & Greeting Based On Hour Of Opening File
Sorry I'm new to this!
Of course all credit to the author for the original code! Very useful![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Sussed it...
Thanks to everyone who helped!
Private Sub Workbook_Open() Dim sName As String Dim sTxt As String Dim CurrTime As Long sName = Environ("UserName") Select Case Environ("UserName") Case Is = "allent1" aTxt = "Tim?" Case Is = "durcatm" aTxt = "Tom" Case Is = "beavern" aTxt = "Nicole" Case Is = "bucks4" aTxt = "Steve" Case Is = "courtew" aTxt = "Wes" Case Is = "fullei1" aTxt = "Ivy" Case Is = "howele2" aTxt = "Elaine" Case Is = "Harrik5" aTxt = "Kerry" Case Is = "stagemn" aTxt = "Nat" Case Is = "newbure" aTxt = "Emma" Case Is = "chaplad" aTxt = "Debbie" Case Is = "neoclek" aTxt = "Karen" Case Is = "tylerb" aTxt = "Badger" Case Is = "staceb" aTxt = "Bev" Case Is = "purclef" aTxt = "Fran" Case Is = "midgelb" aTxt = "Ben" Case Else aTxt = Environ("username") End Select CurrTime = Hour(Now) Select Case CurrTime Case Is > 22 sTxt = "Go home it's gone 10 " Case Is > 18 sTxt = "Good evening " Case Is > 12 sTxt = "Good afternoon " Case Is < 9 sTxt = "Good morning, nice early start " Case Else sTxt = "Good Morning " End Select MsgBox sTxt & aTxt End Sub
I'm using this code
Private Sub Workbook_Open() Dim sName As String Dim sTxt As String Dim CurrTime As Long sName = Environ("UserName") CurrTime = Hour(Now) Select Case CurrTime Case Is > 18 sTxt = "Good evening, " Case Is > 12 sTxt = "Good afternoon, " Case Is < 6 sTxt = "Good morning, wow you're here early, " Case Else sTxt = "Good Morning, " End Select MsgBox sTxt & sName End Sub
to display the desired message "Good morning / afternoon ..." etc but it will only bring up the user id - which in my case is "allent1", I would like it to say "Tim" or have "stagemn" as "Nat" etc...
Anyone have a solution?