Posts by rabsofty

    I'm not sure what you are trying to do after you extract those words? Are you trying to extract other data to put into those columns?
    However, if you simply just want to extract only those words in adjacent cells then

    (formulas to go into adjacent cells)

    =IF(ISERROR(FIND(".",A1,1)),A1 * 60,LEFT(A1,FIND(".",A1,1)-1) * 60 + MID(A1,FIND(".",A1,1)+1,3))

    this is how I did it. (I'm sure there are many other ways to do it!)

    check for the decimal, (ISERROR(FIND(".",A1,1)))

    If no seconds, multiply A1 by 60
    if seconds exist, find the decimal, multiply left of decimal by 60 and add right of decimal

    36 mins and 17 secs = 1277 secs

    Re: Using Variable to call Index of Combo Box

    lxvar =combobox1.listindex
    'where listindex gives you the index of the choice selected  (listindex starts at zero)
     'where lxvar is the variable assigned the listindex 
    'Note a listindex of -1 indicates that nothing was selected
      combobox1.removeitem lxvar

    Re: Userform with 16 comboboxes; "Out of memory"

    I tried your code and do not get the memory error. Maybe providing your spreadsheet to this post might help determine the cause.

    Here is one way to shorten your code.

    Dim y, x As Integer, obj As Object
    'x = column & y= row based on O31
    x = 21: y = Sheet2.Range("O31").value
    For z = 0 To 15
    Set obj = Me.Controls("ComboBox" & z + 1)
    Sheet2.Cells(y, x+z).value = obj.text
    Next z

    Re: BeforeUpdate event triggering as soon as textbox is clicked, in addition to after

    anytime you access or change your textbox, the event is triggered.
    with a textbox's before,after, change,click event, it will always go to the event.
    That means every time you type a character, the event gets triggered.

    to avoid the trigger event issue, simply check it elsewhere and not by a trigger event.

    listboxes and comboboxes events are a little different
    for example for a combo box change event change event can be checked but ignored easily with the following

    private sub something_change()
    if something.matchfound=false then exit sub
    do stuff here if true
    end sub

    with a combobox the event is triggered when it changes and if it does not match it does nothing.
    a textbox is different in the fact it is not looking at one entry at a time, it looks at every character typed

    Re: Convert mmmm d yyyy to dd/mm/yyyy

    If your computer has that date format then you can use.
    (other computers can have different date formats defaulted on their computer)

    d = CDate("February 12 2017")

    but to make sure it's always in the format you want then

    dte = Format(Cdate(thedate), "dd/mm/yyyy")

    where thedate is your date

    Re: using a common class module for cancel buttons across all userforms

    I have readjusted my class so it can make use of all userform objects that I can communize

    class module name = cFrmClk

    in a vb module (at the top)

    Public Fobj() As New cFrmClk, ufi

    in same vb module

    in userform

    Private Sub UserForm_Initialize()
      Call ldbtn(Me)
    End Sub

    How it works:
    the Fobj() As New cFrmClk, ufi
    defines a dynamic array fobj() and a global variable ufi (set to -1 initially)

    when a userform is shown, the userform_initialize routine will call ldbtn(Me)
    the ldbtn(me1) routine will increment ufi and load each control required to the class

    the sub ulo() routine simply erases the fboj() array from memory when the userform is closed

    Re: Copy and paste Special VBA

    I use the the following

    wsSource.Range("B" & lngMyRow & ":I" & lngMyRow).Copy
    wsOutput11.Range("B" & lngPasteRow).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Paste:=xlValues - copies values,
    Paste:=xlPasteFormulas - copies formulas

    for all values, see the help on pastespecial

    Re: Using a class to detect button click and open a form named (buttoncaption)

    I had a similar problem - I called my load buttons into class from the forms


    class module (cFrmClk)

    in a VB module (at the top)

    Public Fobj(1, 3) As New cFrmClk, ufi

    in same VB module (as above)

    in initialize routine of every userform:

    Call ldbtn(Me)

    I set ufi to -1
    when my userform is initialized,
    it calls the ldbtn(Me) - it increments ufi and sets the button(s) click events in the class

    the reason I have a ufi variable (userform index) is my userforms can call an errordlg userform, thus having two userforms at once.
    ufi = 0 sets the butons for the first userform
    ufi = 1 sets the buttons for the 2nd or error userform

    ufi = 2,3,4 etc - if you have 2 or more userforms that open at once (simply adjust the fobj() array accordingly)

    the Fobj(x,y) array
    Fobj(0,y) = 1st userform
    Fobj(1,y) = 2nd userform

    y = 0 to number of obj's you are trapping in ldbtn()

    (I have 2 userforms that can be active and 4 obj's (hence: Fobj(1,3)) (my arrays start at 0)
    once the error userform is closed, I decrement ufi to point back to the first userform

    Re: using a common class module for cancel buttons across all userforms

    I am still working on the cancel click routine removals from the userform (need to figure out mouse events yet)
    I was able to remove all my help button click events from all my userforms and put it in a class module.

    Here is the code

    in a class module (called cHelp)

    in a vb module

    in userform

    'userform calcdlg - 2017\06\02 by Rickb
    Private Sub UserForm_Initialize()
      Call ldbtn(Me)
    End Sub

    how it works!

    ufi variable (userform index) must be initialized to -1

    When you .show the userform, it calls ldbtn(me) -where me is the userform object
    the ldbtn sub routine sets helpbt click event control as well as passing the userform object (me) to the class

    (note my created object class hbtn(1) is an array to store 2 userforms. 1 for the initial userform and 1 in case the error userform is invoked)
    so hbtn(0) holds the main userform objects
    hbtn(1) will hold the error userform objects

    ufi in ldbtn() is incremented to 0 and the class gets set with the main userform help event

    if an error happens that invokes the error userform while the main userform is up, ufi gets incremented to 1
    and hbtn(1) gets loaded with the error useform help event

    (note in my program, the error userform is the only form that loads while another is active)

    in the cHelp class the btngrp_click event will fire when the helpbt is pressed
    it will use the ActUF (passed userform object (Me))
    to get the helpcontext index to pass to the

    note: 1 of my userforms has a multi page so I check for that userform and adj the helpindex (hix) accordingly

    that's it.

    Please note, when you show a second userform, it increments ufi variable, when you unload it you must decrement it by 1
    so it points to the main userform in hbtn(0)

    also, I have help buttons on spread sheets ufi of -1 tells me a userform did not call it
    so that the rhelp routine knows enough to avoid code that has userform references Eg: AcUF

    on help buttons on the sheets can call the clas like this

    Sub FinStmtHelp()
    'hlp btn clked any sht
      Call HBtn(0).rhelp(-1)
    End Sub

    This works great and eliminates all HelpBT_Click Events from all userforms and puts it in a single class module

    I have a Cancel button on all my userforms,
    I would like to create a class module to handle all cancel button click events in any userform I'm in

    I have looked extensively online and all of the code for this is for 1 userform with a bunch of buttons.

    I want to eliminate all the cancel button_click events from all my userforms and use a single class module to do this event.

    My userforms are not dynamically created they were created in the project pane.


    I managed to find my a solution after playing for a while.

    created class module called BtnClass

    'class BtnClass - 2017\05\31 by Rickb
    Public WithEvents ButtonGroup As MSForms.CommandButton
    Public Parent As Object
    Private Sub ButtonGroup_Click()
      x = MsgBox(ButtonGroup.Name): beep
      Unload Parent
    End Sub

    created sub routine in a vb module

    added dim beginning of my main module

    Dim Buttons(1) As New BtnClass

    Re: VBA to Automatically Add to Drop Down Menu and check for Duplicattes

    your if/endif statements are not in proper sequence.

    Re: UserForm Input to a Specific Row

    I have attached a workbook showing how to use indexing in the userform
    the advantage being you can error check fields be putting into the spreadsheet

    I have attached to spread sheets,
    MY_Beta_Housing_Assignment_Tool 2_1.xlsm -uses address to select
    MY2_Beta_Housing_Assignment_Tool 2_1.xlsm -uses housing unit to select