Posts by rabsofty

  • Pull text string between characters

    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)

    Code
    =MID(A1,FIND("/MAR",A1,1)+1,6)
=MID(A1,FIND("/Ex",A1,1)+4,7)

  • Time format conversation from decimal numbers

    Code
    =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


    P.S.
    36 mins and 17 secs = 1277 secs

  • Using Variable to call Index of Combo Box

    Re: Using Variable to call Index of Combo Box


    Code
    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

  • Userform with 16 comboboxes; "Out of memory"

    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.


    <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
    </Code>

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

    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

  • Convert mmmm d yyyy to dd/mm/yyyy

    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)


    Code
    d = CDate("February 12 2017")


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


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


    where thedate is your date

  • using a common class module for cancel buttons across all userforms

    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)

    Code
    Public Fobj() As New cFrmClk, ufi


    in same vb module


    in userform

    Code
    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

  • Copy and paste Special VBA

    Re: Copy and paste Special VBA


    I use the the following


    Code
    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

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

    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


    eg:


    class module (cFrmClk)


    in a VB module (at the top)

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


    in same VB module (as above)


    in initialize routine of every userform:

    Code
    Call ldbtn(Me)


    Explanation:
    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

  • using a common class module for cancel buttons across all userforms

    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)
    However,
    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

    Code
    '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 application.help


    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

    Code
    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

  • using a common class module for cancel buttons across all userforms

    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

    Code
    '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

    Code
    Dim Buttons(1) As New BtnClass

  • VBA to Automatically Add to Drop Down Menu and check for Duplicattes

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


    your if/endif statements are not in proper sequence.


  • UserForm Input to a Specific Row

    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