Posts by Paulbxx

    good morning all,

    just a simple query this I think.. when I have the main work book open I have a macro that opens another excel file for viewing only.

    When the user has finished I want them to exit the view file without saving but not closing down the main workbook. I’ve used this but it shuts spoons excel completely....

    Private Sub CommandButton22_Click()
    Application.DisplayAlerts = False
    End Sub

    Is there a simple code to allow this.



    Hi Mate

    Apologies for the delay in replying as im on nights...anyway I put this code in, tried a few different endings byut I get the same fault....any ideas whats missing....we are defo getting there.

    Private Sub CommandButton1_Click() Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("SETUP") If ActiveSheet.Name = "G BARON" Then sh.Range("D16").Value = Me.TextBox1.Value ElseIf ActiveSheet.Name = "P BARRATT" Then sh.Range("E16").Value = Me.TextBox1.Value ElseIf ActiveSheet.Name = "J CARTER" Then sh.Range("F16").Value = Me.TextBox1.Value If ActiveSheet.Name = "N BAXENDALE" Then sh.Range("G16").Value = Me.TextBox1.Value ElseIf ActiveSheet.Name = "D STOCKS" Then sh.Range("H16").Value = Me.TextBox1.Value ElseIf ActiveSheet.Name = "P BERRY" Then sh.Range("I16").Value = Me.TextBox1.Value ElseIf ActiveSheet.Name = "G ROWE" Then sh.Range("J16").Value = Me.TextBox1.Value ElseIf ActiveSheet.Name = "J WINTERS" Then sh.Range("K16").Value = Me.TextBox1.Value ElseIf ActiveSheet.Name = "R DREW" Then sh.Range("M16").Value = Me.TextBox1.Value Unload changepassword MsgBox "Your PASSWORD has been changed" End Sub

    fault comes up as

    On the last V1 I was asked to change passwords far too much. This way they can do it themselves but if they forget I can revert it back!

    This is the code I have put in to replace mine - however it now updates D16 all the time no matter whose worksheet I am on, have I missed something?

    Private Sub CommandButton1_Click() Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("SETUP") Dim n As Long For Each ws In ThisWorkbook.Worksheets Select Case ws.Name Case "G BARON" sh.Range("D16").Value = Me.TextBox1.Value GoTo X Case "P BARRATT" sh.Range("E16").Value = Me.TextBox1.Value GoTo X Case "J CARTER" sh.Range("F16").Value = Me.TextBox1.Value GoTo X Case "N BAXENDALE" sh.Range("G16").Value = Me.TextBox1.Value GoTo X Case "D STOCKS" sh.Range("H16").Value = Me.TextBox1.Value GoTo X Case "P BERRY" sh.Range("I16").Value = Me.TextBox1.Value GoTo X Case "G ROWE" sh.Range("J16").Value = Me.TextBox1.Value GoTo X Case "J WINTERS" sh.Range("K16").Value = Me.TextBox1.Value GoTo X Case "R DREW" sh.Range("M16").Value = Me.TextBox1.Value GoTo X End Select Next X: Unload changepassword MsgBox "Your PASSWORD has been changed" End Sub

    Try this change to your form code :

    Change this : Set sh = ThisWorkbook.Sheets("P BARRATT")
    To this : Set sh = ActiveSheet

    Just found another bug!!!!

    When the user logs in and just sees their sheet - they can change the password, again I did it as a single sheet then copy etc. I'm having the same issue in that it changes mine every time.....ive looked at the code which is:

    Private Sub CommandButton1_Click() Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("SETUP") Dim n As Long sh.Range("E16").Value = Me.TextBox1.Value Unload changepassword MsgBox "Your PASSWORD has been changed" End Sub Private Sub CommandButton3_Click() Unload changepassword End Sub

    The E16 is obviously where I appear on the setup page- is it possible for the code to pick up the user and insert under their name to change instead of mine??

    Evening all.

    Well so near and yet so far....

    I have attached a toil file which I thought would work perfectly...

    When I log in as an admin I see all the worksheets at the bottom and so on. If an individual staff member logs in they only see their toil record.

    The problem I have encountered is that when they use the ADD / USE toil form then ADD when they are ready they all come onto my sheet P BARRATT! I did set my page up first then copy over and change etc etc. I have looked far and wide, but cant find the solution so that when say Greg put info in it goes onto his sheet as per each individual. Will I have to creat a user form for each person or how do I tell the ADD to use the active worksheet as opposed to P Barratt .

    I have added a working sign in:

    User - Admin

    Password - Chuckles1

    Unlock Sheet - entity

    Unlock VBA - pbarratt6344


    hi Mate,

    Thanks for the reply - the sheet is always visible as i don't want to hide it. I just need the manager to click a button and the sheet unlocks without having to enter the password - does that make sense.



    evening all hope your all doing ok.

    I’ve nearly completed my project, just a couple of I suppose easy things to some but I’m having a problem doing the following.

    When a user logs onto my excel file, they see multiple tabs which are all protected so they can only see the content. On one sheet only I would like a manager to be able to click a hidden button (that I will show them where it is). once they’ve clicked it it unlocks the sheet for editing without the need for entering a password etc. I’ve tried a couple of bits I know but no luck.

    Can anyone help please. Thanks


    hi, I don’t understand where the code needs to go, this is the code I use to send email when they click a button on their page....


    hi jack

    thanks for the message...I have currently got a pop up box message that says email sent when done etc.....where in the code would i put the wait line of code before the message box appears?



    I have a spreadsheet with VBA code in that when someone clicks a submit button, it emails their manager without displaying and then displays message “email sent” etc...

    Is there a way to delay by say 3 seconds the message appearing from pressing the submit button as at the moment it’s instant and some people are wondering if the email went!




    that’s great, thank you very much. I will try all the above tonight at work. I’m still working on the text box issue at the mo where a user when he has opened the add toil form must enter a time as 00:00 and not 00.00 or any other time format when they open the user form to add toil. searching the forums for help...

    Hi mate,

    I put a unload button for now on each form when opened until I can learn where to put the code, like you say at the end but at least it’s working for now.

    Only 1 issue I am seeing at the moment - if you are filling in the data direct onto the table itself, when entering a time it must be in 00:00 format not any other i.e. 00.00 or it returns a run again box etc. Is it possible to put a code on the user from “text box/combo box” that they must put the entry in 00:00 specific format when entering onto the user form of add or use else it will show a vbcritical message?

    I have trawled through ozgrid forums but cant see an answer that might fit?

    hi awsmitty,

    you are correct, I deleted that blank line and all works, thank you. I am now looking at putting some code in so that when they press submit the data goes in to the table as now but then the form closes. Shouldn’t be too difficult I think......he says......would you put the code at the very end of the sub command?

    hi awsmitty

    many thanks for the reply, I understand what you mean.....I think! So when you deleted row 11 then filled out the form and pressed submit it worked?

    I assume that the coding is correct, I.e no errors or omissions so to,speak.

    Thanks again.

    Good evening all,

    I have attached a file that I am currently working on. After 1 week of trying, the coding error has beaten me and I am really stuck - annoyingly stuck I might add. I had some help at the start of this project by a good friend of mine on this forum and I have been to a few classes to update my skills....

    Basically when the employee clicks add new record a new form appears! Great, however when they press submit the data entered does not appear on the table. I'm sure this is just a simple piece of coding I am missing or have put in wrong. The new entries need to start appearing in C12 and so forth etc.

    To access the file when opening select "user" password is "Chuckles 1"

    to unprotect VBA code "pbarratt6344"