|
The purpose is to make a form in outlook containing a command button to print the form to a pre-defined printer.
Is there a function or method in VB scripting or VBA which can be triggered by a command button and causes the form to be printed to a predefined printer (cause its a fax-printer) without letting the user be aware of this choise?
Thank you for taking the time.
Regards,
Dominic Steur.
dsteur@sax-sanitair.be
|
|
|
|
|
Iam trying to create a simple activeX control for the web using Vb. It connects to a backend access database. I have the following code segment that is giving me an error
ConString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=STV250.mdb" & _
"DefaultDir=D:\Inetpub\wwwroot\MastersProject\Inventory;"
objConn.Open ConString
The error being:
[ADO Error # -2147467259
Description [Microsoft][ODBC Microsoft Access 97 Driver] '(unknown)'
isn't a valid path. Make sure that the path name is
spelled correctly and that you are connected to the server
on which the file resides.
Source Microsoft OLE DB Provider for ODBC Drivers]
However if I use,
ConString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=D:\Inetpub\wwwroot\MastersProject\Inventory\STV250.mdb"
objConn.Open ConString
it works fine. That is after I create the .CAB file and embed it in an asp page. But it works only on the computer that houses the webserver and the .OCX/.CAB files.
If I try accessing it from another machine, I get the
ADO Error # -2147467259 again.
I have used the same code for numerous ASP pages and had no problems. But it just refuses to work for the ActiveX control. Could someone please help?
|
|
|
|
|
Can anyone tell me how I can use vba to claculate the number of weeks between two dates please?
I have an excel spreadsheet where the user is prompted to enter a start date and an end date via two input boxes:
duedate = Application.InputBox(Prompt:="Enter first charge date This must be a Monday's date", Default:="02-04-01:", Type:=0)
todate = Application.InputBox(Prompt:="Enter last charge date if different from end of year ", Default:="25-03-01:", Type:=0)
What I would like is to be able to return the number of weeks to another variable say called mult. Then to use this multiplier in a further function.
To complicate matters further I then need to calculate how many of a particular day of a month are in each of the months between the two dates. The day of the month is also requested via an input box as above and held in the variable daymon
To complicate it even further I somehow need to restrict the entries in the first two input boxes to be dates and to be Monday's.
This is so I can create a spreadsheet that tells calculates a weekly charge and then makes it into a monthly payment schedule.
Thanks in advance
Ian
|
|
|
|
|
This seems to work (below) but does anyone know how to return the day of the week. I need to return the date of the next Monday after the current date when the code is ran.
fred = DateDiff("w", duedate, todate)
ActiveSheet.Cells(27, 5).Value = fred + 1
eric = DateDiff("m", duedate, todate)
ActiveSheet.Cells(27, 9).Value = eric + 1
the plus 1 is required as it always returns one week or month less.
Don't suppose anyone knows how to return the day of the week?
I need to make it find the date of the Monday following today's date (today being whatever the date is when the code is ran???
|
|
|
|
|
Here's some code to work with, hope it helps.
Dim i As Integer
Dim datTemp As Date
For i = 1 To 7
datTemp = DateAdd("d", i, Now)
If Weekday(datTemp) = vbMonday Then
MsgBox "Next Monday will be " & datTemp
End If
Next
Andy Gaskell, MCSD
|
|
|
|
|
That's superb thanks - I am using it like this
(Do you know how I can just return the date and not the time too???)
Dim i As Integer
Dim datTemp As Date
For i = 1 To 7
datTemp = DateAdd("d", i, Now)
If Weekday(datTemp) = vbMonday Then
ActiveSheet.Cells(87, 3).Value = datTemp
End If
Next
Do you know how I can return the last Monday in March 2002?
Also any idea how I could do a similar thing with months? Eg to return the next 15th of a month or whatever date the user specifies. Ie if on 17th of June the user wants to return the next 15th of the month it will give 15th July, but if on 14th July the user specifies the 15th it will give the 15th of July.
|
|
|
|
|
This works but I am open to simpler suggestions
Dim TheDate As Date ' Declare variables.
Dim Msg
Dim hey
Dim Ms
Dim instal
Dim instmon
Dim mon
TheDate = Date
Msg = DatePart("m", TheDate) ' Msg now equals the number current month number
dayDate = Date
Ms = DatePart("d", dayDate) 'Ms now equals the current day number
If Ms >= 8 Then
Msg = Msg + 1
End If
instal = 15 & "th "
If Msg = 1 Then
mon = "January"
End If
If Msg = 2 Then
mon = "February"
End If
If Msg = 3 Then
mon = "March"
End If
If Msg = 4 Then
mon = "April"
End If
If Msg = 5 Then
mon = "May"
End If
If Msg = 6 Then
mon = "June"
End If
If Msg = 7 Then
mon = "July"
End If
If Msg = 8 Then
mon = "August"
End If
If Msg = 9 Then
mon = "September"
End If
If Msg = 10 Then
mon = "October"
End If
If Msg = 11 Then
mon = "November"
End If
If Msg = 12 Then
mon = "December"
End If
ActiveSheet.Cells(87, 3).Value = instal & mon
ActiveSheet.Cells(87, 4).Value = "monthly"
ActiveSheet.Cells(88, 3).Value = "15-03-02"
Else
If freq = "weekly" Then
ActiveSheet.Cells(31, 2).Value = (ActiveSheet.Cells(29, 8).Value) / (fred + 1)
ActiveSheet.Cells(31, 1).Value = (("Less " & fred + 1) & " payments @")
ActiveSheet.Cells(31, 8).Value = (ActiveSheet.Cells(31, 2).Value) * (fred + 1)
Dim i As Integer
Dim datTemp As Date
For i = 1 To 7
datTemp = DateAdd("d", i, Date)
If Weekday(datTemp) = vbMonday Then
ActiveSheet.Cells(87, 3).Value = datTemp
ActiveSheet.Cells(87, 4).Value = "weekly"
ActiveSheet.Cells(88, 3).Value = "25-03-02"
'MsgBox "Next Monday will be " & datTemp
End If
Next
|
|
|
|
|
im new to VB, and i really need some help
i want to make an application whos main window is a circle shape and not the regular box shape, how do i do this and is it possible?
any help would be greatly appreciated
also if u know of any sites that help out beginners, please let me know;)
thanks people
|
|
|
|
|
Does anyone know how to validate entries from an input box?
I'd like to ask the user to type in one of three strings. If they type in anything else I would like an error box to pop up and then the procedure to restart.
Can anyone help
This is m code that doesn't work:
Private Sub Workbook_Open()
'gets the rent credit value from user and pastes it into the relevant cell
myref = Application.InputBox(Prompt:="Enter refund, redec or incentive", _
Default:="refund", Type:=2)
myNum = Application.InputBox(Prompt:="Enter amount of the credit", _
Default:=0, Type:=1)
ActiveSheet.Cells(1, 1).Value = myNum
If myref = "refund" Then ActiveSheet.Cells(1, 2).Value = "Refund"
If myref = "redec" Then ActiveSheet.Cells(1, 2).Value = "redec"
If myref = "incentive" Then ActiveSheet.Cells(1, 2).Value = "incentive"
If myref <> "refund" Then GoTo 4
4
If myref <> "redec" Then GoTo 5
5
If myref <> "incentive" Then GoTo 6
6
MsgBox "Error you have not entered a relevant type for credit"
End Sub
|
|
|
|
|
Try this,
Private Sub Workbook_Open()
'gets the rent credit value from user and pastes it into the relevant cell
myref = Application.InputBox(Prompt:="Enter refund, redec or incentive", _
Default:="refund", Type:=2)
myNum = Application.InputBox(Prompt:="Enter amount of the credit", _
Default:=0, Type:=1)
ActiveSheet.Cells(1, 1).Value = myNum
If myref = "refund" Then
ActiveSheet.Cells(1, 2).Value = "Refund"
elseif myref = "redec" Then
ActiveSheet.Cells(1, 2).Value = "redec"
elseif myref = "incentive" Then
ActiveSheet.Cells(1, 2).Value = "incentive"
else
MsgBox "Error you have not entered a relevant type for credit"
endif
End Sub
BTW: Any reason why you are using an edit field, wouldn't a set of radio buttons or a combo box be easier and more user friendly.
The above code is also case senstive which might throw some users.
|
|
|
|
|
I have tried to use a combo box but it goes a bit haywire and I can't get it to work (see earlier post)
Your code is superb thanks.
How could I get it to go back to the input box for the enter refund redec incentive bit instead of just ending.
Your help is very much appreciated
Ian
|
|
|
|
|
I think this should work, loop around until a valid option is selected. I've done it off the top of my head so some of the syntax might be wrong
Private Sub Workbook_Open()
Dim boolCanExit as Boolean
do
boolCanExit = True
'gets the rent credit value from user and pastes it into the relevant cell
myref = Application.InputBox(Prompt:="Enter refund, redec or incentive", _
Default:="refund", Type:=2)
myNum = Application.InputBox(Prompt:="Enter amount of the credit", _
Default:=0, Type:=1)
ActiveSheet.Cells(1, 1).Value = myNum
If myref = "refund" Then
ActiveSheet.Cells(1, 2).Value = "Refund"
elseif myref = "redec" Then
ActiveSheet.Cells(1, 2).Value = "redec"
elseif myref = "incentive" Then
ActiveSheet.Cells(1, 2).Value = "incentive"
else
MsgBox "Error you have not entered a relevant type for credit"
boolCanExit = False
endif
Loop Until boolCanExit = True
End Sub
Michael
|
|
|
|
|
I keep getting loop with out do error message?
Any ideas? This does appear to be what I was after thanks
|
|
|
|
|
I've got this far but the loop remains looping and I have to ctrl alt and delete to kill it:
Private Sub Workbook_Open()
Dim boolCanExit As Boolean
boolCanExit = True
Do
'gets the rent credit value from user
'and pastes it into the relevant cell
myref = Application.InputBox(Prompt:="Enter refund, redec or incentive", Default:="refund", Type:=2)
If myref = "refund" Then
ActiveSheet.Cells(18, 1).Value = "Refund"
ActiveSheet.Cells(32, 1).Value = "91/500 908999"
Else
If myref = "redec" Then
ActiveSheet.Cells(18, 1).Value = "redec"
ActiveSheet.Cells(32, 1).Value = "62/100 225/13"
Else
If myref = "incentive" Then
ActiveSheet.Cells(18, 1).Value = "incentive"
ActiveSheet.Cells(32, 1).Value = "62/100 425/08"
Else: MsgBox "Error you have not entered a relevant type for credit"
boolCanExit = False
End If
rentac = Application.InputBox(Prompt:="Enter rent account number", Default:="1234567", Type:=0)
tenant = Application.InputBox(Prompt:="Enter name of tenant", Default:="Tenant", Type:=2)
Address = Application.InputBox(Prompt:="Enter address of tenant, on one line please", Default:="Address", Type:=2)
payee = Application.InputBox(Prompt:="Enter name of cheque payee please", Default:="Cheque Payee", Type:=2)
payeeadd = Application.InputBox(Prompt:="Enter address of cheque payee, on one line please", Default:="Payee Address", Type:=2)
myNum = Application.InputBox(Prompt:="Enter amount of the credit", Default:=0, Type:=1)
debtorsac = Application.InputBox(Prompt:="Enter debtors account number from Maureen, or enter none", Default:="No Debtors Ac", Type:=1)
debtorsamount = Application.InputBox(Prompt:="Enter amount of the debtors account balance", Default:=0, Type:=1)
ActiveSheet.Cells(51, 2).Value = myNum
ActiveSheet.Cells(10, 1).Value = tenant
ActiveSheet.Cells(11, 1).Value = Address
ActiveSheet.Cells(86, 1).Value = payee
ActiveSheet.Cells(52, 1).Value = "Debtors" & debtorsac
ActiveSheet.Cells(52, 2).Value = "-" & debtorsamount
ActiveSheet.Cells(38, 1).Value = rentac
End If
End If
Loop Until boolCanExit = True
End Sub
Help
|
|
|
|
|
I think the boolCanExit = True needs to be inside the Do loop
Private Sub Workbook_Open()
Dim boolCanExit As Boolean
Do
boolCanExit = True ' will always exit unless the user enters incorrect value
'gets the rent credit value from user
'and pastes it into the relevant cell
myref = Application.InputBox(Prompt:="Enter refund, redec or incentive", Default:="refund", Type:=2)
If myref = "refund" Then
ActiveSheet.Cells(18, 1).Value = "Refund"
ActiveSheet.Cells(32, 1).Value = "91/500 908999"
Else
If myref = "redec" Then
ActiveSheet.Cells(18, 1).Value = "redec"
ActiveSheet.Cells(32, 1).Value = "62/100 225/13"
Else
If myref = "incentive" Then
ActiveSheet.Cells(18, 1).Value = "incentive"
ActiveSheet.Cells(32, 1).Value = "62/100 425/08"
Else: MsgBox "Error you have not entered a relevant type for credit"
boolCanExit = False
End If
rentac = Application.InputBox(Prompt:="Enter rent account number", Default:="1234567", Type:=0)
tenant = Application.InputBox(Prompt:="Enter name of tenant", Default:="Tenant", Type:=2)
Address = Application.InputBox(Prompt:="Enter address of tenant, on one line please", Default:="Address", Type:=2)
payee = Application.InputBox(Prompt:="Enter name of cheque payee please", Default:="Cheque Payee", Type:=2)
payeeadd = Application.InputBox(Prompt:="Enter address of cheque payee, on one line please", Default:="Payee Address", Type:=2)
myNum = Application.InputBox(Prompt:="Enter amount of the credit", Default:=0, Type:=1)
debtorsac = Application.InputBox(Prompt:="Enter debtors account number from Maureen, or enter none", Default:="No Debtors Ac", Type:=1)
debtorsamount = Application.InputBox(Prompt:="Enter amount of the debtors account balance", Default:=0, Type:=1)
ActiveSheet.Cells(51, 2).Value = myNum
ActiveSheet.Cells(10, 1).Value = tenant
ActiveSheet.Cells(11, 1).Value = Address
ActiveSheet.Cells(86, 1).Value = payee
ActiveSheet.Cells(52, 1).Value = "Debtors" & debtorsac
ActiveSheet.Cells(52, 2).Value = "-" & debtorsamount
ActiveSheet.Cells(38, 1).Value = rentac
End If
End If
Loop Until boolCanExit = True
End Sub
|
|
|
|
|
Thanks
It needs the end ifs moved up under the if bits and the loop put above the rest of the code and now it works fine.
Thanks for your help. The examples on the microsoft website were very poor. Your help has been invaluable.
|
|
|
|
|
Dear Experts
I want to customize the fonts used in Outllok like the font used in Text Boxes of
To field,Cc Field,Subject Field and the overall headers...i dont know how to do it..but ive created a form but its not being used by default.Any suggestions
Mahesh Sundararaman
|
|
|
|
|
I am trying to put a combo box onto an excel spreadsheet. The idea is so this bit of the code picks up.Select Case ComboBox1.Value
Case 0 '
ActiveSheet.Cells(1, 1).Value = "refund"
Case 1 '
ActiveSheet.Cells(1, 1).Value = "redec"
Case 2 '
ActiveSheet.Cells(1, 1).Value = "incentive"
But I can't put the code for the combo box onto the workbook open part wqithout an error. If it remains on theindividual sheet on the ComboBox1_Click() sub it does't update. Also my combo box code:
ComboBox1.AddItem "Refund" 'ListIndex = 0
ComboBox1.AddItem "Redecoration Allowance" 'ListIndex = 1
ComboBox1.AddItem "Tenant Incentive" 'ListIndex = 2
'Use drop-down list
ComboBox1.Style = fmStyleDropDownList
for some reason makesmultipple entries on the drop down box instead if jyust the three. Can anyone help please?
|
|
|
|
|
I think you need to look at the ComboBox1.ListIndex, to retunr the index no, starting a 0, if you've used AddItem, without specifying an index.
Hope that helps.
Giles
|
|
|
|
|
Any idea how I might do that?
Thanks for your help.
|
|
|
|
|
'Create a combo box on the worksheet called ComboBox1 and copy and paste this....
'Make sure that youtr not in d3esign mode when you use it as nothing happens
Private Sub Worksheet_Activate()
'Initialisation Code
With ComboBox1
.Clear
.AddItem "Dog" 'Index 0
.AddItem "Cat" 'Index 1
.AddItem "Mouse" 'Index 2
End With
End Sub
Private Sub ComboBox1_Change()
Dim i As Integer
i = ComboBox1.ListIndex
Select Case i
Case 0
Cells(1, 1).Value = "This is a Dog"
Case 1
Cells(1, 1).Value = "This is a Cat"
Case 2
Cells(1, 1).Value = "This is a Mouse"
End Select
End Sub
Giles
|
|
|
|
|
The With ComboBox1 .Clear bit is what I was missing and why mine were adding entries until infinity.
Many thanks.
|
|
|
|
|
Yep, becuase every time to Activate the workbook they are added. There is and Initialize event for a Workbook, but thats the only one to support the once only event. You could initialse it from there when the workbook opens.
Giles
|
|
|
|
|
How can I check if the user has pressed a specified button then change it to something else.
|
|
|
|
|
When someone pushes a button, an event has occured. So, if they click on a button named command1, you could put code in the "OnClick" event that will update the text of your text box like:
Private Sub Command1_Click()
Text1.Text = "You clicked the button."
End Sub
|
|
|
|
|