Posts by LionelNZ

    Re: Clear Contents Of Cell Based On Comparsion Of Cell Values

    Apologies if I have but I have just reviewed my posts and i cannot see it in my list of threads unless it was so long ago that I have forgotten it AND it is not shown in my recent posts. If you can give a link to my earlier post & it has the answers then I will remove my post.

    I have searched the forum for answers as well as the net & I have J walkenbach's Bible on macros but have not been able to find the correct coding for what I want.

    I keep a library of code & I use the macro recorder to get the syntax and I experiment a lot with code before I post.

    But If I have transgressed oops & sorry again.

    Hi everybody,

    I am a financial administrator & every month I have down load 4 bank accts as CSV, import

    into xl & code (CACode) the amounts for our accountant.

    I have VBA that formats, adds headings & formula etc but I have a problem/s.

    In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA).

    Now I am trying write a macro to clear the contents of the cell in I col
    if eg h60 = h59
    then I60 clearContents, Select h59
    Else select H59
    Do until H3 is selected

    Select table (A2:I Xldown)
    Sort Table by CAcode (H Col)
    Set Range as H3:Xldown
    Select Last cell with CAcode (Xldown) in H col
    For every cell in Range (H3:Xldown)
    Use If/then
    [INDENT]If Last cell = 2nd Last cell (H Col) then
    Clear contents of I col (last row)
    select 2nd Last cell (H Col)
    Else 2nd Last cell (H Col)[/INDENT]
    Next Cell

    I have to use Xldown to select range as the range will be variable each month & for each bank acct.

    I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals

    Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.

    Thanks in advance

    Re: Renaming Workheet as date format (cell reference) using VBA

    Thanks. As I said my code was messy from experimenting etc.

    I did a bit more searching & come up with the solution as you suggested. For the benefit of others here is my complete code (cleaned up)that now works.

    Hi Folks.

    I download & import CSV files/sheets from bank, visa accts etc & I am trying to automate the processes I use including ensuring I (& others) will use the same naming conventions eg visa "yymm" = V0701, Direct Cedits = DC0701 etc.

    I am having trouble renaming or ensuring the active sheet is named according to the date in A3 eg in the example I want the sheet to be renamed V0702 as the date in the cell is 02/02/07 "dd/mm/yy" format (imperial system).

    In BOLD is what I have & the renaming is messy becuase I searched the forum, Walkenbach's bible & help menu.

    Re: Focus To Return To Textbox On Error

    Thanks for this. The data is timesheets & does not have to be very secure as it is only to stop prying eyes on others timesheet & to prevent management somehow changing the timesheets. I di it this way as I use a master time sheet that looks up staff details (including pwrd) then a macro saves the sheet as a staff Name & copies & paste all the data from lookup as a value, thereby hardwiring the info to that sheet. That is why the pwrd is in that range.

    Re: Save As Every Date?

    Do you mean that you have a master file & you need to save this master file ONCE every day as mm-dd-yy GC Reconciliation?

    Or do you mean that you need to save a master file every day for for EVERY day of a required period (calendar year, financial year etc)?

    Either way it is relatively easy just need further clarification.

    Solved :-))) Focus To Return To Textbox On Error

    The above is the offending code with the [COLOR="Red"]Exit Sub[/COLOR] in the wrong place. Below is the code that works : D

    I will post the whole worksheet with my test pwrds as i know there is fine tuning to do but this will work for the users as they are not overly XL literate.

    The purpose of this sheet is to have staff fill in a timesheet while making it idiotproof, limited acess for user, read-only for management by having user insert only one password to gain appropriate access. This is necessary because staff want to be assured that only they

    have rw access & only mangement can only view timesheet without making changes as the hese timesheets sit on a public folder on the server.

    I will post the file using ths msg number as the reference. I have had a few battles with this code & it can probably be tidied up heaps as long as it meets allof the above constraints.

    Pwrds -

    User has limited rights (incl copying & pasting graphics for signature) with some unlocked

    cells on sheets 1-12 = "staff"

    Management = read only on all sheets = "mlvl"

    Author/developer - full access = "s"

    When distributing this worksheet I protect the VBA.

    I hope this helps other users!

    Re: Focus To Return To Textbox On Error


    [B]If Response = vbYes Then 

    I will give this a go
    Cheers Big Ears!!

    Hi Folks.

    I have SS that I have 3 levels of users using/viewing data and at the
    moment it does not allow for errors when inputing password & sheet
    number to access. What I need is that if there is an error in password
    or sheetnumber ths user gets the choice to re-enter correct data with
    vbYesNo or similar. The problem I have is with these lines of code not
    getting the focus back to txtSheet to re-enter correct data (>0 & <13).

    Re: Converting Numbers Stored As Text To Numbers Via Macro

    Quote from firefiend

    But of course.

    Sub macro()
    Range("F:F").Select 'specify the range which suits your purpose
    With Selection
    Selection.NumberFormat = "General"
    .Value = .Value
    End With
    End Sub

    This macro will take the range and convert those pesky string numbers to numerical data.

    O r you could use something lik ASAP utilities ;)

    BUt i must admit it is fun getting code to do what YOU wnat!: D

    Solved Re: Protected Sheet - Insert Picture File

    I have solved this problem by inserting the jpg file (which is a user's signature) into an unlocked cell of the sheet. Then the user can copy/paste the graphic into the appropriate cell. :music:

    Hi everybody.

    My problem relates to this post
    I have managed to write simple but effective code that allows only one password to be enetered to open a SS & offers 3 levels of protection, limited permission to enter data, read-only & data completely hidden.

    This is excellent for my needs however I want the user who has limited rw to be able to insert a pic file which is their signature but because the sheet has protected cells it won't allow user to insert picture jpg file.

    Is there a way to do this?

    I have just thought of a creative solution tho. What if the picture file is already saved in the workbook and by using code to ask to insert pic file it copies from one location in SS to the appropriate cell in the worksheets?

    The scenario is that I have fortnightly timesheets in 12 calendar months in 12 separate sheets. When staff member fills in timesheet I want them to insert signature for that f/night indicating thatthey have filled it in & it is correct but because of protection it won't allow me to.

    BTW I can post code/file if required

    Re: Workbook Password Protect 2 Levels

    This is what I have to try an open a workbook with one password only (but different passwords giving accesss to different sheets).

    What is happening is that I usually need "s" to open workbook which will give access to Sheet1 with the inputbox asking for password.

    Now it doesn't matter which password is given but both sheets (2 & 3) become unhidden.

    Any help on this would be appreciated.

    Re: Workbook Password Protect 2 Levels

    Thanks for your help but having tried various combinations I still cannot get the workbook to open or readonly with one password. Psuedo code of what I am trying to do.

    Workbook is completely protected (no read or write access)
    On workbook open
    Ask for password
    If password = "readonly" pwrd then open as readonly
    If password = "write access" then open with read/write access.

    I want any user to only have to use one password to get approproiate workbook access.


    Re: Workbook Password Protect 2 Levels With 1 Pwrd

    Quote from Ranger

    Hi Lionel,

    I know you say you don't want to use the Save As, but if you use Save As and tick the Read Only Recommended checkbox, then you only have to put in one password, if you want access to it, if not just click the Read Only button.


    The problem is Bill that the files will be stored in a common folder on the server and staff do not want others reading their timesheets. By using the Save as option there has to be one password to open & view the file then a second password change data.

    I have to setup passwords, store them & then distribute them correctly to all staff & management so if I can develop (with a lot of help from others) VBA code so that staff only have one code to give them write/save access & management read only access it will make a life a lot easier.

    Cheers anyway as I have code to try out.

    Hi there.

    On opening a workbook (XL2003) I want users to enter a password
    which will give them read Only permissions or access to the whole

    I do not want use the "save as" option it is not suitable for my needs & here is why.

    I have about 10 staff who need to fill in timesheets using XL SS on a public folder on the server. The staff need full access to file & management such as myself only need to view (read only) the timesheet without the ability to change data. Using the "save as" function the staff need to input 2 passwords.

    The staff (in general) are not overly computer literate so I was hoping to be able to write code so that on Workbook_Open event only one password is entered and depending on the password gives the user full access or readonly access. This will also save management having to remember different passwords for read only access to different staff timesheet files & will give crude protection to the files.

    Here is the code

    Error occur on "Workbook.ReadOnly" lines:confused: .

    I also want to put an errorchecking code for invalid passwords.

    Again, I know I can use the "save as" option but that is not what I need.


    Lionel DownUnda

    Re: Select a variable Range Macro - Resolved

    Here is what I got after fiddling

    I actually want the cursor to go back to the originating cell of the selection e.g T20 etc: D .