Posts by bnix

    Re: ListBox Control


    Try


    Code
    1. strName = lstMailTo.List(lstMailTo.ListIndex, 0) 'first column
    2. strEmail = lstMailTo.List(lstMailTo.ListIndex, 1) 'second column

    Re: Serial date conversion to semi string data.


    It is probably overkill, but if you wanted to use a function with Insomniacs suggestion of CDate() it is simple enough to change my original one;


    Code
    1. Function ConvertTime(ByRef dtValue As Date) As String
    2. If IsDate(dtValue) Then
    3. ConvertTime = Format$(dtValue, "mmddyyyy_hhmmssAM/PM")
    4. End If
    5. End Function

    Re: Serial date conversion to semi string data.


    You can try this



    you can pass in a cell value and it should do what you want

    Re: Copying formulas


    Welcome to the forum!


    If you are wanting to fill down with the formula you can use;


    Code
    1. =IF(OR($C1="",$B1=""),"",($C1+$B1)-$A1)


    There are other conditions that you can add but this is a simple example. Also, if your values are staying the same when you autofill, it sounds like you have your calculation set to manual and you would use SHIFT+F9 to calculate the current sheet.

    Re: Dim'ing Userform ComboBoxes


    Untested..but you can try



    You could also loop through the controls of the form and if it is a combobox then set the array at that time instead of having to hard code each Set line.

    Re: Running Record Count


    Still working on my first cup of coffee so I am not sure I am following exactly, but you could Filter the recordset as you mentioned and when you do so it does moves to the first record of the filtered recordset.

    Re: Modify a resulting ADO.Recordset


    Quote from iwrk4dedpr

    I wonder!!


    Can you run a "SQL" command agains an existing recordset. Seems like it should be possible. I've just never seen it done.


    No, you cannot, at least not with ADO, you can Filter it, but you cannot query against an existing recordset.

    Re: Modify a resulting ADO.Recordset


    Look at the .GetRows() method, it returns an array from your recordset and allows you to pull in only the field you are wanting.



    This might be useful for you if you are not wanting to loop through the records.

    Re: Modify a resulting ADO.Recordset


    If you are using ADO, you could do something along the lines of;



    This is untested but this should meet what you need. Again, if you are using ADO, you might also look at the .GetRows() method.

    Re: Creating a Primary Key using VBA


    You can also use another SQL string to do it


    Code
    1. CREATE INDEX NewIndex ON <table_name>(<field>) WITH PRIMARY


    If you were using multiple fields for the primary key you would use


    Code
    1. CREATE INDEX NewIndex ON <table_name>(<field_one>, <field_two>) WITH PRIMARY

    Re: Copyright


    More than likely, the company purchased the office license that your boss was using to develop the applications and his work was related to company information, so my thoughts are that this would probably fall under the whole intellectual property legalise category, of which I know nothing!


    I have created many applications for my company and in the event I leave I simply take copies of my code and or applications and as Jack said leave the rest for them to figure out if any troubles arise. If problems do occurr and they need your bosses input, then he could charge them a consulting fee. Without a contract, I would not think your boss has a leg to stand on and the company can simply state that as an employee any thing he did is now company property.

    Re: Importing data automatically


    Try this.


    Put this formula into C8 of the Preload workbook and copy down. You will need to change the path of the Master workbook.


    =VLOOKUP(B8,'C:\<change to directory path on your machine>\[master.xls]Carrier #'!$A$7:$B$10,2,FALSE)

    Re: Copy part of a row to a new row


    You can try this.


    Code
    1. With Range("A1").End(xlDown)
    2. .Resize(1, 11).Copy Destination:=Range("A1").End(xlDown).Offset(1, 0)
    3. .Offset(1, 10).Select
    4. End With


    This is assuming you have no blank entries in Column A.

    Re: Can a VB app run VBA code within a workbook?


    If I understand the question, are you saying you essentially want two identical projects that are running the same code, only you want one to run uninterrupted, while in the other you are stepping through the identical code?


    Even in VB6, you would have to have two seperate IDE windows open in order to do what you are asking.


    If you are wanting to do this, you could copy the workbook, rename it, then open both VBE windows in both workbooks, run the first one, then you can step through the other one. YOu would need two seperate XL windows open, do not open the books in the same application window though.

    Re: Macro Behaves Differently on Second Run


    I think Rich_z may have identified your problem.


    From your description


    Quote


    If I quit Excel and relaunch it, it runs perfectly again even though code and source data have not changed in the first place.



    It sounds like you have some global or modular level variables that are not being cleared when entering the procedure again after the workbook is open.

    Re: Skip if cell is emtpy or is not only a number


    You could also try



    You would probably have to adjust your looping structure so if the check skips a line you do not end up with a empty item in your array.

    Re: Large and Small


    If there is some code behind the live feed you could do something there such as making the window automatically minimized or the application visible = false, but otherwise I am unaware of anything at this time to always keep the active window on top.

    Re: replacing n/a


    Welcome to the forum.


    You can try this.


    Code
    1. =If(IsErr(LOOKUP(M9,Sheet2!D3:F11)*(M9-Sheet2!E11)),"",LOOKUP(M9,Sheet2!D3:F11)*(M9-Sheet2!E11))