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 SubObviously 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 SubWithout seeing your GetAttachments code it's difficult to comment further but you could run your check on the folder / item description etc here.
there are some useful third party programmes you can use for this
I have tested out the below which works well, and can be programmed automatically from VBA (ie turn on prior to batch mail and off once mail complete)
see below for full details
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...
ie
Private Sub CONTROL_NAME_Click()
Dim sheet_name as string
sheet_name = the sheet on which this control is located.....
End SubSo 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.
Thanks
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...
Solution...
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...
eg
Private Sub workbook_open()
application.vbe.activevbproject.vbcomponents.import filepath & "/routine.bas"
'within routine.bas will be a module containing a macro called say macro1
call macro1
End SubObviously 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?
Cheers
Luke
EDIT
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
Sub Pop....(stc as combobox, st as string)
With stc
.clear
End With
etc...
End SubGuys,
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"
POPULATE_COMBOBOX(st)
End SubThe 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
Sub POPULATE_COMBOBOX (st as string)
Dim v as string
Dim rw, i as integer
Dim cb as combobox
Select Case st
Case "DOMAINS"
cb = DOMAINS
Case "STATES"
cb = STATES
End Select
With Sheets("REPORTS").cb
.Clear
End With
rw = 2
i = rw - 2
Do Until Sheets(st).Cells(rw,1) = ""
v = Sheets(st).Cells(rw,1)
With Sheets(st).cb
.AddItem v,i
End With
i = i + 1
rw = rw + 1
Loop
End SubSo in short how do I refer to a control (in this case a combobox) using a variable?
Anyone have a solution?
Thanks
Luke
PS Geez, I've had to ask a question 
mwb...
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?
eg...
Sub ....
10
I1 = InputBox("Enter Product Number to Find")
if len(I1) = 0 then goto 10
r = application.worksheetfunction.match(I1,range("A:A"),0)
cells(r,2).copy
End SubIf 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 FunctionAll 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.
are you doing this in VBA?
If so can you post what you're using up on to the board.
You will need to run VBA to copy any modules etc you're running for specified sheet into the new workbook.
Steve - I am asp / asp.net / 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...
http://www.ozgrid.com/forum/sh…d.php?p=114052#post114052
Then an example query would be like this....
Sub GET_DATA()
Dim c as adodb.connection
Dim r as adodb.recordset
Dim sq as string
Set c = new adodb.connection
c.open "DSN=ORACLE_CONN"
'your sql query
sq = "Select * from table1"
Set r = c.execute(sq)
If r.eof = false then
'get field headings from db
For i = 0 to r.fields.count - 1
Cells(1,i+1) = r.fields(i).name
Next i
'return recordset in one lump to Cell A2
cells(2,1).copyfromrecordset r
'close recordset
r.close
End If
'close connection to db
c.close
End SubHope that gets you started.
The above should sit in a module in VBA.