Posts by lasw10

    on the userform in VBA right click and "View Code"

    Here you can place your events for when various elements in the userform change...

    eg to make C = A-B

    Private Sub tbxB_Change()
    tbxC.Value = tbxA.Value - tbxB.Value
    End Sub

    Obviously the above is not validating the values in A & B but you get the basic idea...

    in adjacent cell (assuming you don't have any John T Bloggs entries) - ie in A1 Joe Bloggs - entering the below in B1 would return Bloggs, Joe

    =MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)

    If you did have John T Bloggs then you could use VBA InStrRev function (to find last space)

    Once you have your 500 names in Column B just copy them and paste special VALUES ONLY over existing names in A - and delete Column B

    well at the start of your code application.activeprinter will be the default will it not?

    so you could store that as a variable.

    defaultprinter = application.activeprinter

    .... change printer....

    'reset to default..
    application.activeprinter = defaultprinter

    well I would guess that either Column A (on the formula sheet) is text or Column A on the lookup table sheet is text...

    Try to ensure they are the same... but if A9 on your formula sheet is text and the lookup table is values then convert to value using if(value(a9)>0,vlookup(value(a9),....)

    Let us know.

    any reason why you're doing this in 5 minute intervals?

    can you not simply run your getattachments event when new mail is received?

    You would set this up within "ThisOutlookSession" - then select Application (left drop down box) - and NewMail (right drop down box) - will generate...

    Private Sub Application_NewMail()
    End Sub

    Without seeing your GetAttachments code it's difficult to comment further but you could run your check on the folder / item description etc here.

    thanks Andy - for some reason i thought that method was not working before... it appears i have a different problem than first envisaged (which was that if you didn't activate a cell on the sheet prior to calling event the active sheet name wasn't being recognised from within the event and not parsing properly).

    Quick Q

    Anyone know the syntax for establish which page a control is sitting on...


    Private Sub CONTROL_NAME_Click()
    Dim sheet_name as string
    sheet_name = the sheet on which this control is located.....
    End Sub

    So within the event for the control I would like to be able to determine which sheet the control is sitting on... I do not want to hardcode it.


    Very possibly Dennis but I avoid arrays like the plague... in short I am doing this "thing" for another dept and am running short on time... so I tend to stick with what I know (albeit a limited platform!!)

    One last thing...

    Anyone know how I can edit the code within a module programmatically?

    Scenario is I have 2 large modules which I would like to store as .bas files outside of the file.

    When the file is then opened I import the 2 .bas files.

    However... as part of my workbook open event I need to call a module that has yet to be imported so it falls over...


    put an apostrophe before the call routine until the module has been imported - then remove the apostrophe so as to be able to call it...


    Obviously VBA reads the call when compiling and says that MACRO1 doesn't exist and falls over (error handler not sufficient) ... so I would like a way to start the open book event as

    'call macro1

    and after the import has been done remove the apostrophe so it becomes
    call macro1

    Is this possible? Or does someone have an alternative idea for me?



    I have created an intermediate solution which was to create a module I will keep within the file called SIDESTEP_CALL which is run on open

    which in turn calls the module I have since imported in the open...

    but obviously I then need to keep this new module within the file which I'd rather not do...

    Thanks Dave,

    in the end I just decided to parse the combobox name through (which isn't ideal) but works


    I want to know how I can set a combobox control to be dynamic...

    ie I want one common module that populates a combobox where the combobox is determined via a parsed variable to the routine...

    eg one module determines the value to be parsed and calls the populate routine as below

    Sub ....
    st = "DOMAINS"
    End Sub

    The populate routine uses a select case statement to determine the combobox this routine should be applied to... (st is a sheetname incidentally)

    The below is what I had thought might work but it won't recognise the variable cb

    So in short how do I refer to a control (in this case a combobox) using a variable?

    Anyone have a solution?


    PS Geez, I've had to ask a question :)


    hey there...

    when you see create a count etc do you mean create a new column in your table or simply run a query that will contain the new values (ie position & sum)?

    so you're using a send key method?

    well rather than use the Find Window why not use an Input Box and Match VBA function?


    If you explain the next bit we might be able to do that through VBA also (ie rather than copy it manually - actually post the value in B to your other system automatically).

    agreed with Richie but if you would like a vba solution you could create a simple function

    so in F19 = GetValue(B19)

    and copy down in Column F for all your sheet names in B

    then insert a vba module with the following

    Function GetValue(ws As String)
    GetValue = Sheets(ws).Cells(5, 1)
    End Function

    All you're doing is parsing the sheetname to the function (from Column B - ie Sheet2, Sheet3 etc)

    It then goes to that sheet to predetermined cell (5,1 = A5) and returns the value.

    Hope that helps

    Not perfect and I am sure someone will havea more succint version but it works... all you need to do to add more countries etc is enter any additional countries you want to display in Column A on Sheet DATA and the respective capital in Column b on the same sheet.

    Then press the button on the first sheet to update the lists with the new data.

    Attached as zip file.

    Steve - I am asp / / html etc literate (as is XL-Dennis)... but I am not quite sure I follow how you're doing this...

    Where are you getting the data from that you then load to Oracle (ADO I presume as per previous discussions?)

    You can use ADO in ASP or ADO.Net in ASP.Net.

    Feel free to mail me what you have... also suggest you pop up a more detailed explanation of exactly what you're doing currently and what you would like your asp page to do.

    WillR etc.. suggest moving this thread to SQL development forum?

    Joe we never did get to the bottom of this did we...

    If you direct access to the SQL2000 db then (if your IT dept allow it) you can set up an ODBC DSN connection to the remote server - the host of the server should be able to give you the IP of the server which you use as the hostname - then enter the database name, username & password etc...

    Once the DSN is set up the earlier code is the same

    You basically need the host to give you the SQL you need to run to extract the data you want rather than having to go to the webpage and make your selections via web controls (drop down lists etc).

    The other way you could do it is if the hosts would allow you to parse a SQL query through a URL which would execute and return the data to the report page which you could then return to XL via a WebQuery.

    OK this is far easier than you would think...

    I use ADO to connect and retrieve data from MySQL all the time but Oracle is no different - except for the SQL syntax.

    Let's say you set up an ODBC connection to your Oracle DB called "ORACLE_CONN" (to save a long connection string)

    Then you would reference the Microsoft Active DataX Library (highest you have) - see the below link for an example of how to do this via workbook open events etc...…d.php?p=114052#post114052

    Then an example query would be like this....

    Hope that gets you started.

    The above should sit in a module in VBA.