Posts by rockettpunk

Re: Auto Refresh Active Worksheet

Perfect!!

Thanks!

Hi again!

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

Tim

------------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:

Have fun!

(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!

Thanks all!

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!

I used

Private Sub UserForm_Activate()
Me.WebBrowser1.Navigate2 "Home page address goes here"
End Sub

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.

Hi again,

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,

Tim

Re: Random Messagebox Generator

Exactly what I wanted!

it's the

lngIndex = Int((10 - 0 + 1) * Rnd + 0)

bit that I just couldn't sus!!

Thanks!!

Hi guys!

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!

Perfect!

Hi!

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

Re: Display Username & Greeting Based On Hour Of Opening File

Thanks for the speedy reply!

How would i go about using the

Application.UserName

in the code I am already using to get the desired results as isn't that code only used for setting the username?

(silly question maybe, but hey...)

Hi guys!!

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?