|
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
|
|
|
|
|
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
|
|
|
|
|
I found what I thought to be an easy way to shove a graphical VB form to a printer.
It works fine when I call ShowPrinter and change the print quality and click OK.... But I don't know how to pick up the Cancel button click of the CommonDialog1.ShowPrinter and avoid printing.
Here's the code:
CommonDialog1.ShowPrinter
Form1.PrintForm
(need to detect cancel w/in CommonDialog1.ShowPrinter - branch around Form1.PrintForm if Cancel is pressed)
Any ideas?
Thanks in advance,
\\Jim
CommonDialog1.ShowPrinter
jamesdturner@hotmail.com
|
|
|
|
|
Try this:
On Error GoTo ErrHandler
CommonDialog1.CancelError = True
CommonDialog1.ShowPrinter
Form1.PrintForm
Exit Sub
ErrHandler:
End Sub
If you have existing Error Handling code the ErrHandler would look a little different:
ErrHandler:
If Err.Number <> 32755 Then '32755 is the Error Number for Cancel being selected
'Process Error as usual
End If
End Sub
Andy Gaskell, MCSD
|
|
|
|
|
hi all,
i want to write a small macro... that can generate a csv from the excel file... well not the whole of worksheet but just the selected cells.
for example... something like this...
1, 2, 3
1, 5
4, 8, 9, 5
1
2
3,4
6, 7, 9, 10
1, 2
so if u ppl can guide me for writing this macro in VBA for Office.
tks in adv.
regards
sajid;
|
|
|
|
|
First all, I am a C++ programmer working with some VB code that I am not the best with, is there a way to create a stand alone VB .exe that will take command line parameters?
i.e. like the old Dos stuff "dir /s /d" <- I want to create an executable called dir and pass it the parameters "/s" "/d"
|
|
|
|
|
Taken from MSDN:
Function GetCommandLine(Optional MaxArgs)
'Declare variables.
Dim C, CmdLine, CmdLnLen, InArg, I, NumArgs
'See if MaxArgs was provided.
If IsMissing(MaxArgs) Then MaxArgs = 10
'Make array of the correct size.
ReDim ArgArray(MaxArgs)
NumArgs = 0: InArg = False
'Get command line arguments.
CmdLine = Command()
CmdLnLen = Len(CmdLine)
'Go thru command line one character
'at a time.
For I = 1 To CmdLnLen
C = Mid(CmdLine, I, 1)
'Test for space or tab.
If (C <> " " And C <> vbTab) Then
'Neither space nor tab.
'Test if already in argument.
If Not InArg Then
'New argument begins.
'Test for too many arguments.
If NumArgs = MaxArgs Then Exit For
NumArgs = NumArgs + 1
InArg = True
End If
'Concatenate character to current argument.
ArgArray(NumArgs) = ArgArray(NumArgs) & C
Else
'Found a space or tab.
'Set InArg flag to False.
InArg = False
End If
Next I
'Resize array just enough to hold arguments.
ReDim Preserve ArgArray(NumArgs)
'Return Array in Function name.
GetCommandLine = ArgArray()
End Function
If you need help changing this code around to fit your needs, just reply. Basically everything in the command line gets passed in by the Command Function.
|
|
|
|
|
I created a database in Access 2000. I tried to use the following code in VB 6.0 to open that database:
---
Dim wspDefault As Workspace
Dim DB As Database, DBSet As Recordset
' Grab the default workspace.
Set wspDefault = DBEngine.Workspaces(0)
' Open the database.
Set DB = wspDefault.OpenDatabase("c:\temp\xxx.mdb")
---
When I run it, though, it gives me error 3343: "Unrecognized database format".
Is this because VB does not have / know the correct Jet DLLs to access a database created in Access 2000? They must exist otherwise how is Access doing it... If this is the problem, how / where do I get the right files, and where do I put them? If not, what have I done wrong?
|
|
|
|
|
I'm guessing that you're using DAO, and I don't know too much about DAO. Make sure you have the latest version of DAO referenced in your project. Get the latest version of MDAC and Jet 4.0 SP3 here: http://www.microsoft.com/data/download.htm
Otherwise, if you're willing to use ADO, I can come up with some code for you. Get the latest MDAC either way tho 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
