Posts by TheShyButterfly

  • Excel Userform: UK (dd/mm/yyyy) format not working when written to cell

    Hi There, thank you for checking out my problem.


    Object of the form:
    When date is entered (dd/mm/yyyy) in the date textboxes, displaying dd/mm/yyyy in the textbox, it retains the format when it is submitted to the spreadsheet in the next empty row.
    I will also be creating a ‘find record’ (in a multipage form), where when a user enters the search criteria, it populates the date textboxes in the correct dd/mm/yyyy format.

    The issue is that when a date is entered into the textboxes Txt_StartDate and Txt_EndDate as dd/mm/yyyy, that when they are submitted to the worksheet, they revert to the mm/dd/yyyy or the date as a text format.

    ‘Normal’ dates 1st day to the 12th day of the month goes to mm/dd/yyyy format in the sheet, but dates from the 13th day to the 31st (or last day of the month), are stored in the sheet in TEXT format.


    Current Coding based in the Calculate button:



    I have tried the following settings on the worksheet cell/column formatting;

    • No formatting at all (which should defaultly store the date as a date as per the system setting (dd/mm/yyyy) – stores as Text or American (US) date format
    • Formatting cells/columns to dd/mm/yyyy still switches the date to the US date format

    VBA coding attempts:
    I have tried formatting the code of the textbox date

    • Using the cdate format,
    • Format(Txt_StartDate.Text, "dd/mm/yyyy")
    • Format$(date1,"dd/mm/yyyyy")

    None of the attempts work.

    Currently the code is in the ‘calculate’ button, but I have tried it in the Sheet Sub and Module Sub and neither of those worked either.
    Now I am more frustrated & confused.

    • My system time zone is (UTC+10:00) Canberra, Melbourne, Sydney, in the date format of dd/mm/yyyy.
    • Excel 2016

    I have been struggling over 18 months trying to find a solution for successfully converting/formatting dates entered in my Userforms into the UK (dd/mm/yyyy) date format into the worksheet.

    I have tried and tested in all likeliness ALL the YouTube tutorials and various forums that promote that they have the solution, but none have fixed the problem on my system (or my work computers).

    Could I ask a small favour, that when you test the proposed solution, that you change your system time zone to one that uses the dd/mm/yyyy date format and test dates under and past the 12th day of a month, just to confirm that it does what it is intended to do.
    If you could advise me if there is a way that I can code the date format as a global default, that would be fantastic. Just let me know where the code has to be written (eg, userform, module, worksheet, workbook).


    I've attached the sample file for your reference.


    Thanking you in advance,
    Kind regards
    TheShyButterfly

  • Excel VBA 2016 32bit - Only creating new job # if sheet is completed

    Re: Excel VBA 2016 32bit - Only creating new job # if sheet is completed


    Hi Informage,
    Thank you for responding to my query.
    Unfortunately, no it doesn't do what I'd like. The addition of the Messagebox asking user if they want to Create a new Work Order, only takes them to the Menu page, which I had already setup to be the sheet to be displayed when the workbook opens.
    I have made some changes in my original post that I hope will give a clearer insight into what I was hoping to get to work (LOL) ...


    Thank you for your time and effort ... I have replaced my sample file with the file you updated and I have made minor cosmetic changes to make it easier to relate to, plus when you look at the Service Log you'll see the duplication of Work order numbering.

  • Excel VBA 2016 32bit - limiting scroll area doesn't work

    Hi there,


    I am trying to limit the scroll area of a particular sheet, Menu. I only want people to be able to scroll from A1:A1 which I have enlarged to fill the window. It appears to work. but when I close the file and re-open it, the scroll area is not locked down.


    I have tried 2 different methods:


    1. in the Worksheet Properties window I have set it to A1:A1 (but when I reopen file after saving, the range is blank).



    2. in the VBA module I have entered

    Code
    Sub LimitScrollArea()
cnMenu.ScrollArea = "A1:A1"
End Sub


    but that too does not lock when file is re-opened.


    I thought this was a simple fix, however it's turning into a nightmare of frustration LOL :)


    Any assistance would be gratefully received.


    Cheers,
    ShyButterFly

  • Excel VBA 2016 32bit - Only creating new job # if sheet is completed

    Hi There,


    I have uploaded a revised sample file with some minor cosmetic changes, including the msgbox prompt that Informage created, because I could see that there may have been some confusion.


    I have also tried to simplify the workflow requirements so that it may make more sense of what I am expecting from the workbook.


    I have an 'Email Form' set out in a worksheet. When the workbook opens, it generates a new Work Order Number automatically, and when the macro button is clicked (Submit Request), it opens up an Outlook email with the cells layout embedded in the email, which is then reviewed and sent and the workbook is saved and is closed.


    The problem is I am getting duplication of Work Order numbers and/or jumping numbers ahead when no 'Work Order request has been made.

    The process I was trying to get was the following:


    Workbook Opens with Menu sheet (options)
    o Log New Work Request (opens the 'Email Form sheet')
    o Update Service Log (opens the Service Log sheet for updating records)


    If New Work Request:
    • Opens up the “Email Form” sheet, with the next incremental work order number (derived from the last work order number from the Service Log sheet)
    • When the ‘Submit Request’ button is clicked, this will complete the process of copying the data to the ‘Working’ and copied into the Service Log and required information populated in the Outlook email Subject field. The file is then saved and workbook closes.


    If Updating Service Log
    • Opens up the Service Log sheet and enables the user to update whatever record data as required and saves the file.


    Ideally what I would love is when the Log New Work Request button is pressed (from the Menu sheet), then the Email Form should activate and have the new Work Order number displayed in the Work Order No. field.


    The other problem I have noticed is that if the user changes their mind, from updating the Service Log to wanting to create a new request, the Job number is not incremented when the Email Form sheet is selected, which causes duplication of the work order numbers. Obviously we can't risk duplication of Work order numbers.


    Is there a fix for this? I hope I haven't confused you more ...




    There is code in ThisWorkSheet, and in modules ModEmailWorkOrder and ModWorkbookControls.



    As is probably evident of the coding, I am not very proficient with VBA ... still in the beginner category.


    I would appreciate any assistance and/or recommendations.

  • Excel 2016 32bit VBA: Copying a range of cells into HTML Outlook Email

    Re: Excel 2016 32bit VBA: Copying a range of cells into HTML Outlook Email


    Hi Carim and KjBox,


    I did eventually realise that I had forgotten to copy over the 'Function' routine, and once I copied that over, it pretty much went sweetly ... just have a few other bits to fix and the project will finally be complete ... however, depending on the level of frustration and mental fatigue, I might be back :)
    But thank you so much for your advice ... I had heard some excellent reports about Ron's site ... but didn't have the time to research on this occasion .. but I will keep him in mind (bookmarked it) :)
    Thank you for your time and perseverance - excellent service and feedback in a very timely manner.
    Kind regards,
    TheShyButterfly

  • Excel 2016 32bit VBA: Copying a range of cells into HTML Outlook Email

    Hi there,


    I am trying to create an email from within VBA that will select the range of cells from A1:M43.
    The range of cells are laid out like a form (refer to image) [ATTACH=CONFIG]72959[/ATTACH]


    I obtained code from an existing project that works perfectly, but when I copy the code into my module and edit it to reflect my "Email Form" sheet, I get the Compile error "Sub or Function not defined" when I step through the code.


    I have turned on the MS Outlook and HTML in the Reference Library.


    In my module, I have the 'Option Explicit' heading, but in the module from which I copied the code, there is no 'Option Explicit', which would account for me reason I get the error, because it wasn't declared, but I don't know how to declare "RangetoHTML" which is at the point the code breaks.
    The code I have so far is:



    I'd be grateful for any assistance.
    Cheers,
    TheShyButterfly

  • Excel 2016 32bit VBA: Creating automated Work Order number based on Year Month

    Re: Excel 2016 32bit VBA: Creating automated Work Order number based on Year Month


    Hi again Carim,
    My final objective is to have cell E16 of the 'Email Form' worksheet display the next sequential number of the work order in the format stated in my post

    Quote

    The final format of the work order number must be displayed as SWC201706-00001 (etc).

    .


    I also mentioned that

    Quote

    Once an ActiveX button is clicked on the worksheet named ‘Email Form’, then the data from the ‘Email Form’ should be copied into the next available empty row on the ‘Service Log’ worksheet, save and close the workbook (starting from row 3).


    So to summarise:
    Objective/s:
    1. Have VBA auto-increment the work order number in cell E16 in the worksheet "Email Form". Must be formatted as SWC201706-##### whatever the number is.
    2. When the ActiveX command button is clicked, the data from the form, in this case, the Work Order number, is moved to the worksheet called 'Service Log', in the next empty row in Column A.


    I don't know how much more I can explain it ... create sequential job number with prefix SWC and moving the data to the Service Log (column A of the next empty row) when the command button 'Email Form' has been clicked. I've attached a screen shot, with a red line around the command button, which will move the data to the Service Log, save and close the form.
    [ATTACH=CONFIG]72919[/ATTACH]
    Thank you for tweaking the code, and I no longer get any error messages, but nothing is happening at all to the worksheet. I tried running the code with E16 blank, and nothing happened. I tried entering a five digit number above 5 (201806), and still nothing. I entered in SWC201706-00001, and nothing happened. I checked the formatting of the cell, which was set to text, but even when I changed that to Number or General, there was still no input of data anywhere. I note that your code doesn't provide for the prefix of SWC.
    Sorry for your frustration.

  • Excel 2016 32bit VBA: Creating automated Work Order number based on Year Month

    Re: Excel 2016 32bit VBA: Creating automated Work Order number based on Year Month


    Hi Carim,
    I pasted in your code, but got a compile error message: "Wrong number of arguments or invalid property assignment".
    The area highlighted is the Left, and I don't know the reason for it. I don't know if there will be other errors if this error is resolved.
    SWJobNo = Left(Range("E16"), Len(Range("E16")) - 5 & Format(Right(Range("E16"), 5) + 1), "00000")


    I apologise for being painful ... I don't mean to be ... I've been trying to resolve this problem for the last 2 weeks on my own, only ending up in frustration ... hence this forum is my last straw.

  • Excel 2016 32bit VBA: Creating automated Work Order number based on Year Month

    Re: Excel 2016 32bit VBA: Creating automated Work Order number based on Year Month


    Hi Carim,
    I was just trying to work that out, trying all sort of things, like creating a new sheet and placing SWC in cell A1, 201706 in cell A2, '-' in cell A3, and '00001' in A4, then concatenate the cells together in A5, which did alright until the last 5 digits and trying to add one to the figure.
    But that in itself also creates confusion for me .... trying to get the format of the Workorder number correct, and then getting it coded from VBA to take the contents of the cell and add 1, then coding to populate the log sheet on the next available line. My VBA skills are just not advanced enough to do that I'm afraid. There must be some VBA code that can do this without having to generate another worksheet, concatenating cells and placing results in the Service Log.
    There must be an easier way .....

  • Excel 2016 32bit VBA: Creating automated Work Order number based on Year Month

    Hi there,
    I am trying (unsuccessfully) to create a work order number in cell E16 in the Email Form worksheet, that gets automatically incremented each time the worksheet is opened.
    The final format of the work order number must be displayed as SWC201706-00001 (etc).

    I initially tried to create a Userform, however that was too problematic because I was getting errors. So I thought I’d use a more direct method of creating it and assigning it to cell E16 and wasn’t successful.

    The work order number is based on the prefix ‘SWC’ and then on the current year, current month and then the next sequential number of the last five numbers.

    Once an ActiveX button is clicked on the worksheet named ‘Email Form’, then the data from the ‘Email Form’ should be copied into the next available empty row on the ‘Service Log’ worksheet, save and close the workbook (starting from row 3).
    I copied some code from another workbook that behaves in the same way as I need my workbook to function, but I get syntax errors, which highlights the following code line:
    SWJobNo=left(activecell.Value,len(activecell.value)-5&format(right(activecell.Value,5)+1,"00000"


    And


    SWJobNo=format(right(activecell.Value,5)+1,"00000"


    Full code so far in module1


    I am getting desperate and frustrated because my skills are so limited, which is not your problem, but I am on a deadline and that is this week :( I didn't think this was going to be so complicated. I've attached my sample file.


    I do appreciate any help you may be able to offer.
    Thanking you in advance.
    TheShyButterfly

  • Excel 2010 VBA Userform - Multiple clicks on button adds multiple records - same No.

    Re: Excel 2010 VBA Userform - Multiple clicks on button adds multiple records - same


    Hi RoyUK,
    You got me ... LOL ...
    I have been SO into getting this form to work (still things to be ironed out :) ) that I had forgotten that the 'Unload' me will save me ... :) ... I did have it remarked out because I was testing something else .. DOH! apologies :) ... won't happen again :)


    Thank you for responding so quickly ... I am sure I will have some 'real' questions as I am nearing exhaustion in creating this monster and learning VBA at the same time ...


    Cheers,
    TheShyButterfly

  • Excel 2010 VBA Userform - Multiple clicks on button adds multiple records - same No.

    Hi there,
    Thanking you in advance for your time and effort :)


    I have a userform with a textbox that has an 'incident No.", and a 'save' button which transfers the data from the form to the spreadsheet (Incident details). The Incident number is transferred to the next available row in Column A.



    The problem that arises is when the 'save' button is pressed mulitple times (in case the user is not sure whether he has 'saved' the information), the same incident number is entered in the next available row ... creating duplication (multiple times) of the same record.



    What I would like is:
    If the record already exists, then I just want it to overwrite the existing information if it has been changed, thus keeping the record number unique.


    The code below is from the userform code module



    Please advise if you require any further information ... I am not that proficient with VBA (still learning). I have tried researching possible solutions and adapting them to suit my naming conventions and cell/row references, but I haven't been successful :(


    I'd appreciate any assistance.
    Cheers,
    TheShyButterfly