Posts by maria90

    Re: Retrieve Current Controls Event


    yes, sorry Roy
    what I meant was whether it is possible to determine the type of event handler; without me having to hardcode it.
    in my example, the sub would automatically determine TypeOfAction as being an exit handler.


    If it is possible to retrieve the sub's name, I could put it into a string and check whether it has the word "_exit" in it.

    I know that it is possible to retrieve the current control's name by using the ActiveControl.Name.


    However, if the control is in a frame, I always get the frame's name instead of the control I am in - for example a textbox called txtDate.


    Is there a way to determine the current control's name even though it is inside a frame?


    Thanks


    Maria

    Dear all


    Is it possible to retrieve the current control's type of event?
    Is it an Enter or Exit event?


    For example:



    I know that it is possible to retrieve the current control's name - how about the type of action?


    Thanks


    Maria

    Re: Class Programming: Objects on Userforms


    Hello cytop


    Thank you so much for your code.
    I have adapted it and I have one more question.


    The code assumes that a partial date is always in the current year.
    Is there a way to change this? To make it check only once a full date has been entered?

    Dear Experts


    In order to make my controls to look nice, I'd like to change the border color when the user enters and exits a control.
    I've created the following two subs

    Code
    1. Sub GetEnterListbox(ByRef lst As MSForms.ListBox)
    2. lst.BorderColor = vbRed
    3. End Sub
    4. Sub GetExitListbox(ByRef lst As MSForms.ListBox)
    5. lst.BorderColor = vbBlack
    6. End Sub


    And I put the code into the onEnter/onExit of the listbox.


    I'd have to create subs for each type of controls.


    Is there a way to create a neater sub?
    For example, a sub that just takes any object and then checks for the type of object? Like:




    Or is there a better way to create a sub that traps the onEnter/onExit event automatically, without me having to call a sub each time?
    Some userforms have two and more listboxes and textboxes.


    Thanks for your help.


    Maria

    Hi


    What is the best way to lookup a value in a listboject?


    For example :
    How do I get a phone number of customer 12?


    [ATTACH=CONFIG]69437[/ATTACH]
    Table Customers


    I would normally use an array:
    Look up in which position the number 12 is and then use the cell offset to retrieve the phone number.


    However, as this code is implemented in a userform which has a number of array lookups, I have the impression that this method slows down the opening of userform.


    Is there any other way which you suggest I should use?


    I have attached a sample file.


    Thanks


    Maria

    Dear all


    I use a textbox called date on a number of userforms. The textbox checks on exit whether the date is a holiday.. with some additional action.


    I am an absolute beginner with class programming but it is my understanding that you can create a new instance of a class, with all the properties associated with the class.


    Is it possible to create a textbox class called txtDate which checks for errors when the user exits the textbox?
    I assume that the exit function would also need to be a class?


    This would enable to me to reduce the code needed in my project .. most likely by 20% as I could then apply the same technique to other controls.


    Isn't this the idea of class programming?


    I have attached an Excel file with many userforms .. each with a textbox called txtDate
    The function checks whether a date is a holiday.


    I do sincerely hope that someone can help me with this.


    Maria

    Dear Experts


    My customer list contains geo coordinates which are retrieved with a custom function.
    Occasionally, there are some errors: missing values, error messages etc. For this, I have created a userform which contains a list of all customers whose GPS data need to be 'fixed'. That is: connect to Google server and retrieve lat/lng again.


    As the progress can take up to 5 seconds per query, I thought it would be nice if I could display a progress bar somewhere on the screen. Users will thank me for this if there are hundreds of customers whose data needs to be updated.


    This code retrieves the data from the listbox and with a query, retrieves the coordinates from the Google servers:



    How would you integrate a progress bar which progresses whenever the new coordinates have been saved? (bChecked = true)


    Thanks for your help


    Maria

    Files

    Dear Experts


    I have a listobject and I'd like to extract the unique values from a column and put them into an array.


    For example:


    The array is called arrCustomers.
    What is the correct procedure if I need to retrieve the unique values from Orders[CustomerID] and put them into arrCustomers?


    Thanks for your assistance.


    Maria



    Example Table


    [TABLE="width: 256"]

    [tr]


    [td]

    Date

    [/td]


    [td]

    Time

    [/td]


    [td]

    CustomerID

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    13:30:00

    [/td]


    [td]

    51

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    14:30:00

    [/td]


    [td]

    51

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    14:00:00

    [/td]


    [td]

    46

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    12:00:00

    [/td]


    [td]

    51

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    11:45:00

    [/td]


    [td]

    51

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    11:00:00

    [/td]


    [td]

    45

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    10:20:00

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    11:00:00

    [/td]


    [td]

    42

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    11:30:00

    [/td]


    [td]

    42

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    12:55:00

    [/td]


    [td]

    68

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    11:20:00

    [/td]


    [td]

    17

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    09:40:00

    [/td]


    [td]

    55

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    09:30:00

    [/td]


    [td]

    55

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    10:00:00

    [/td]


    [td]

    46

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    10:30:00

    [/td]


    [td]

    46

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01.03.2016[/TD]

    [td]

    10:10:00

    [/td]


    [td]

    55

    [/td]


    [/tr]


    [/TABLE]

    Files

    Re: Create an Enter/Exit code for All Objects on a Userform


    Right... now lets assume that I have


    5 textboxes
    2 listboxes
    1 combobox


    I could set the backcolor of the object to blue on Exit and set it back to a default color on Exit.
    In my example, I would have to create 16 additional procedures (2 for each object x 8 objects).
    Needless to say that this is tedious and hard to manage.


    Is it possible to create 1 or 2 procedures per userform that would handle this?

    Dear experts


    I'm a bit confused with listobjects.

    While I know how to add news records to the listobject (just add the data underneath it), I do not know how to change existing data (changing value, deleting records) inside a listobject.


    The listobject is called tblProducts and it contains columns ProductID,Name,Price etc. and the data is entered with userforms.


    How do I change a value if I know the ProductID?


    I need to find out in which row the ProductID is with Application.Worksheetfunction.Match, right? For example, ProductID 88 is in row 8
    From row 8, I need to know the columns ... and that where I don't know how to go ahead, as I have to use the column names?


    I see that listobjects contain "DataBodyRange". I assume that this is for the value inside the listobject?
    Do I need to use DataBodyRange?


    I've created a workbook with some sample data and a userform, hope this helps.


    Thank you so much for your help.


    Maria

    Files

    • sample.xlsm

      (21.39 kB, downloaded 116 times, last: )

    I have a listbox lstCustomers which is populated with an array (lstCustomers.list = arrCustomers).
    The first column is the customer ID, the second is the name etc.


    If I want to highlight/select a value in the listbox, using lstCustomers.Value = x does not work.



    • How do I select a value in a listbox if the listbox is populated with an array?
      I assume that I loop through the listbox, checking in column 0 for a match and then select/highlight it?
    • Is it better to use arrays or shall I use the regular rowsource method instead? The customer listbox has 1200 entries.


    Thanks for your assistance.

    Re: VBA Calculating Week of the Month based on Number


    That's interesting: It works in the spreadsheet - but when use it in VBA, I get a wrong result.


    Example: 07/03/2016 - According to the calendar, it's the first Monday of March.


    So with the spreadsheet formula, I get 1
    With the code, I get 2


    I live in Europe where the week starts on a Monday.
    Could that be an issue?

    Re: VBA Calculating Week of the Month based on Number


    Why did we choose this method?


    We use GroupWise as our calendar. For recurring dates, you can choose in which week of the month it needs to repeat.
    But as we use Excel for recording orders and dates etc, I was hoping to implement the same method. So I thought: If you can do it in GroupWise, you must be able to do it with Excel.

    Re: VBA Calculating Week of the Month based on Number


    No, not quite


    Another example:


    If I'm given a date and it is for example 16/03/2016 I know it is a Wednesday.


    But which Wednesday of the month is it?
    Based on the calendar, it's the third Wednesday of the Month.


    So I was hoping to write a formula which gives me a numeric value, in this case: 3


    So for us, it means that the deliveries are always on the third (because it's calculated) Wednesday (I can derive the weekday from the date) of the month


    Hope that helps a bit. :)