Posts by ABTAX

  • Resize Named Range Lists Simultaneously

    Re: Resize Lists Simultaneously


    AAE, hello again.


    This is the same workbook you kindly helped me with yesterday and uploading it is not an option.


    I did not actually intend to upload anything previousy and the notes were for for information only, ie to explain that the parent list / range will always have a first data row (with the next cell in row A showing the '*' sign as is the norm for lists).


    I no doubt confused matters though with the final sentence in my original post (an editing error on my part) and I have deleted it.


    Thanks again

  • Resize Named Range Lists Simultaneously

    Surely an easy one but I can't quite work it out.


    There are 4 lists containing accounting data and within each list the data rows beneath the headers are named ranges.


    The parent input list is on the Payments worksheet with headings in A3:R3 and the data rows underneath are a named range entitled Payments.


    The child lists contain formulas to pull the required information from the parent Payments list. These lists are identically sized and headed with headers in A1:K1. The data rows underneath the headings are named ranges entited Debits, Credits and VAT. The worksheets on which each resides have the same names.


    I am looking for a way to automatically contract / expand the child lists as each row is added / deleted from the parent Payments list, ie so each list always contains the same number of rows.
    The formulas already within the child lists will then ensure that the apprpriate data is copies to them from the parent Payments list.


    Note that the named range Payments will always contain at least a single row to ensure the retention of the required formulas and validation.


    Note also please that there is a macro which automatically resizes the range Payments to a single row when a command button is pressed.


    Any assistance would as always be appreciated.

  • Clear Value Cell & Leave Formula Cells

    Re: Clear Values But Protect Formulas


    AAE, I've spent what seems like 3 hours now typing a reply to your first post, but as I was doing so it hit me that I'm being really stupid somewhere. Telling you this may save you a few minutes of reading my first intended reply! It was getting very long winded. I'll post it anyway though so you can see where I'm coming from in case it helps.


    Simplifying this, the list I want to delete, whilst still retaining it as a list with the required formulas / validation etc, has headers in row A3:O3. Data is entered from row 4. I only need code which deletes every row from 5 onwards and which changes the inputted values in row 4 to zero. That seems incredibly obvious and easy and i feel like an eejit now :)


    That's not to say of course that I'll achieve my aim, but if nothing else, I've better recognised the problem.


    The formulas therein are incidentally within the input rows themselves and there are no formulas in the spreadsheet outside of the list, apart from row A which contains the name and code of the nominal account that is currently being edited.


    In most cases, if a value is entered in one cell, that formulaically (is that a word?) enters details in another cell within the row. Eg, enter figures for a transaction with 17.5% VAT and other cells are filled in showing the net / VAT / gross amounts. As is necessary though, the user can change some of thse cells (in this example the VAT, since the real figure may differ by a penny or 2, or since more than 1 VAT rate may be involved.


    I'll post my initially planned reply for more info, but this reply is probably more pointed :)


    Many thanks for your advice thus far. It amazes me when people don't say thank you on these forums and seem to post as if it was a legal entitlement for them to receive an answer without offering any courtesy. Maybe I've been lucky, but every reply I've ever received to my posts has been helpful and all have been much appreciated, as is yours.


    Cheers bud[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Shg, I only read your post after pontificating for hours over my previous planned response and real response to AEE.


    Your suggestion looks so obvious that I'd be annoyed, if it wasn't for the fact I'd still never have thought about it no matter how much time I wasted (without asking anyone else I mean). Now the macro is deleting all but the first row of the range and I it should meet all my aims with a little adaptation. I'll advise further soon even if only for info.


    In the meantime, many thanks to you and AEE, its people like you guys that get us novices started and stop us giving up. I don't actually feel like a complete novice now, having done a lot of vba coding etc, but I only know what I know and haven't exactly learned the syllabus. I've been on this project for a wee while now and its at the near complete stage but I'd certainly have wasted many, many more hours (actually, days) if it wasn't for the ozgrid members helping me out.


    Thanks chaps (or chapesses if I've got that wrong :)

  • Clear Value Cell & Leave Formula Cells

    The worksheet (Payments) contains a list that allows the user to input accounting transactions. Almost every input cell contains a formula or data validation enabling selection from a dropdown.


    The worksheet feeds the data to 3 separate spreadsheets (Debits / Credits / VAT) and the formulas for that are contained within dynamic ranges in the other 3 sheets. A command button macro then feeds that data from the 3 sheets to the Master sheet. This all works perfectly.


    After the macro is run and the data is copied to the Master sheet, I would like all of the data in the 4 other sheets to be cleared without losing the formulas and data validation, ie so that the sheets are empty and clear for the next batch of inputs.


    Hoping someone can point me in the right direction please...

  • Set DTpicker To Todays Date

    Re: Set Dtpicker Todays Date


    Think I've sorted it:


    Code
    DTPickerInvoicesDate.Value = Now()


    Not properly tested yet but appears to be ok at first glance.


    Thanks for your responses Zimitri.

  • Set DTpicker To Todays Date

    Re: Set Dtpicker Todays Date


    Only the code that was automatically inserted when adding the control, ie:


    Code
    Private Sub DTPickerInvoicesDate_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)


    There is code elsewhere which dicates when the control is visible, when it is enabled and to return the selected date to the appriopriate textbox/ cell etc (all of which works).

    I experimented with code along the following lines but to no avail:


    Code
    DTPickerInvoicesDate.Value = Format(Now(), "dd/MM/yy")

  • Dynamically Create Named Ranges Based On Cell

    Re: Name Ranges Dynamically


    P45cal, many thanks. Tried and tested and works perfectly.


    To trouble you again, do you know of a way that I can implement this code for every line within the worksheet, without having to enter the code again for each specific row?


    Ideally, I would like code that will create a new range name whenever any individual narrative in column C is changed (currently approx 120 rows but will ultimately be more).


    Hope this makese sense! Thanks again.

  • Dynamically Create Named Ranges Based On Cell

    Hello all


    I am looking for a solution to dynamically create named ranges according to the contents of cells in a particular column.


    The following code works for 1 word names, but in many cases the title cell contains numerous words separated by spaces. Is there a way I can adapt this code so that it will name the ranges with the spaces removed? For example, where cell C2 contains the narrative 'Sales Ledger Control', I would want the range name 'SalesLedgerControl'.


    Code
    Range("R2:Z2").Name = Range("C2")


    The named ranges are referred to in numerous other worksheets, where selecting a particular narrative from a listbox creates a dependant drop-down in the adjacent cell (eg if Sales Ledger Control is selected, the dependant list contains names of customers).


    FYI, the data validation in the other worksheets ignores the spaces in the range names, ie:


    =INDIRECT(SUBSTITUTE(G2," ",""))



    It would not take me long to name the ranges manually, but a macro is preferable because the narratives in the title cells will often change and the range names will obvioulsy also therefore change.


    Any help would be appreciated as always.

  • Format Textbox Uk Date

    Re: Format Textbox Uk Date


    Gents,


    Think I've sorted my problem.
    I removed the text box control source property and it now reads from the same named cell in text format. The textbox does not now update the source cell and that is fired by an additional line in the DTPicker change event code.


    My thanks as always for your input.


    Code
    Private Sub UserForm_Initialize()
txtDateOfIncorporation.Value = Range("DateOfIncorporation").Text


    Code
    Private Sub DTPickerDateOfIncorporation_Change()
txtDateOfIncorporation.Value = Format(DTPickerDateOfIncorporation.Value, "dd/MM/yy")
Range("DateOfIncorporation").Value = DTPickerDateOfIncorporation.Value

  • Format Textbox Uk Date

    Re: Format Textbox Uk Date


    Quote from royUK

    Why is your example code not working?


    Roy, the code does does indeed work in correctly populating the text box. It is when that date is transferred to the worksheet that it reverts to the US date format, even if the cell is UK date formatted.


    The text box is linked to the cell via the control source property (hence the reason there is no specific code other than the DTPicker change event). The textbox therefore also shows the date in the US format when it reads it from the worksheet.

  • Format Textbox Uk Date

    In my Excel VBA user form, there are multiple instances of the MS DTPicker control, which feed multiple text boxes. These in turn are linked to named cells on an worksheet. Note at this stage please that dates will not always be required, hence the text boxes. Only the button on the DTP controls are visible, alongside the related textbox.


    My problem is that the values in the cells are in US date format and I need them to be recognised in the UK date format. The dates therein must be formatted as dates (ie not as text), as they are used throughout other worksheets for calculations.


    In the sample below, DTPickerDateOfIncorporation is the named cell (the control source).


    I know from Roy's responses to other questions on the DTPicker that he doesn't use it - is there a better alternative?


    Any help will as always be much appreciated.


    Code
    Private Sub DTPickerDateOfIncorporation_Change()
txtDateOfIncorporation.Value = Format(DTPickerDateOfIncorporation.Value, "dd/MM/yy")
End Sub

  • Dtpicker In Multipage

    Re: Dtpicker In Multipage


    Roy, I shall persevere a little longer. I had it working (ie many other fields on subsequent pages of the multipage) when using text boxes instead of DTPickers, but they're not ideal. I'll keep trying though...


    Thanks for your help, much appreciated - and even if I didn't iron this out, your advice helped me tidy up a lot of unnecessary bulk in my code.

  • Dtpicker In Multipage

    Re: Dtpicker In Multipage


    Roy,


    Thanks again. When I started on this project many moons ago (which includes hundereds of other lines of code I didn't show), I knew absolutely nothing about VBA - even less than the little I know now...


    Some of the code is therefore unnecessarily messy. Thanks to your advice though, I have considerably neatened it by removing the ActivateCell lines and having one line of code to update each figure as you suggested, eg


    Code
    Range ("Cell").Value = controlname.value


    All else works perfectly but I still have the same run-time error with the DTPicker on the second page (if I remove the code for that control there is no error).


    The relevant lines of code are:


    Code
    Range("E27").Value = Me.MultiPage1.Page1.DTPickerDirector1DateOfBirth.Value


    Code
    DTPickerDirector1DateOfBirth.Value = Format(Range("E27").Value, "dd/MM/yyyy")


    I've tried using variations on the themee (eg MeMultipage1.Pages(1)...) but all to no avail.


    Any ideas much appreciated (again).

  • Dtpicker In Multipage

    Re: Dtpicker In Multipage


    Roy,


    Sorry for confusion! I don't actually have 2 different Initialize and cmdFinish codes. Bad copying and pasting on my part! This will make more sense:


    Code
    Private Sub
DTPickerAccountsStartDate_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
End Sub


    Code
    Private Sub
DTPickerAccountsEndDate_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
End Sub


    Code
    Private Sub DTPickerDirector1DateOfBirth_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
End Sub


    Code
    Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Cover").Activate
DTPickerAccountsStartDate.Value = Format(Range("B6").Value, "dd/MM/yyyy")
DTPickerAccountsEndDate.Value = Format(Range("C6").Value, "dd/MM/yyyy")DTPickerDirector1DateOfBirth.Value = Format(Range("E27).Value, "dd/MM/yyyy")



    Many thanks for your time.

  • Dtpicker In Multipage

    In a Multipage form, the first page contains 2 DTPickers. These show the date value from the specified excel worksheet cells and update the cells accordingly if the user changes the dates with the TDPicker. These work perfectly.


    If I place another DTPicker control on another page of the Multipage though, it leads to a run-time error.


    As an example, the code for 1 of the successful DTPickers on the first page:


    Code
    Private Sub DTPickerAccountsStartDate_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
End Sub


    Code
    Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Cover").Activate
DTPickerAccountsStartDate.Value = Format(Range("B6").Value, "dd/MM/yyyy")
End Sub


    Code
    Private Sub cmdFinish_Click()
' Updates Multipage 1 Company Information
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Cover").Activate
Range("C6").Select
ActiveCell.Value = DTPickerAccountsStartDate.Value



    The code for the DTPicker on the second page was written in exactly the same manner, ie

    Code
    Private Sub DTPickerDirector1DateOfBirth_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
End Sub


    Code
    Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Cover").Activate
DTPickerDirector1DateOfBirth.Value = Format(Range("E27").Value, "dd/MM/yyyy")
End Sub


    Code
    Private Sub cmdFinish_Click()
' Updates Multipage 1 Company Information
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Cover").Activate
Range("E27").Select
ActiveCell.Value = DTPickerDirector1DateOfBirth.Value



    There are no errors in any of the cell references etc and the formatting of the DTPicker controls is exactly the same in all cases.


    I've searched high and low without any success. If anyone can point me in the right direction...

  • UserForm & Controls: Allow Spreadsheet Editing

    I have an Excel workbook containing various pages that I want the user to edit within a form in a datagrid type layout. The typical spreadsheet page (ie Excel list) is 25 columns with hundreds of rows of records.


    The spreadsheets themselves contain formulas in the majority of cells.


    Any suggestions? I've read a lot about datagrids and the spreadsheet control but none the wiser.


    Any advice will as always be appreciated!