|
Hi,
I am using input boxes to enter data into a spreadsheet. But my problem is that if the user makes an error then they can't go back. This is partly because after the user has input the data my code puts their data into variable and then does various calculations with it before putting the result into defined cells.
Does anyone know how I could put in a user update if they right click the cell where they have made an error? The new data would then have to be refrshed through the rest of the code. Heres an example of some of the code
Private Sub Workbook_Open()
Dim mydate As Date
Dim baldate As Date
Dim duedate As Date
Dim todate As Date
Dim TheDate As Date ' Declare variables.
Dim Msg
Dim hey
Dim Ms
Dim instal
Dim instmon
Dim mon
mydate = Date
ActiveSheet.Cells(15, 1).Value = mydate
rentac = Application.InputBox(Prompt:="Enter rent account number", Default:="1234567", Type:=1)
tenant = Application.InputBox(Prompt:="Enter name of tenant", Default:="Tenant", Type:=2)
adda = Application.InputBox(Prompt:="Enter house no and street, eg 1 Walnut Street", Default:="1 Walnut Street", Type:=2)
addb = Application.InputBox(Prompt:="Enter name of Town, eg Prudhoe", Default:="Prudhoe", Type:=2)
AddEx = Application.InputBox(Prompt:="Enter additional address lines, eg Hexham", Default:="Prudhoe", Type:=2)
addc = Application.InputBox(Prompt:="Enter name of County, eg Northumberland", Default:="Northumberland", Type:=2)
addd = Application.InputBox(Prompt:="Enter postcode, eg NE42 123", Default:="NE42 123")
bal = Application.InputBox(Prompt:="Enter opening balance", Default:="100.0", Type:=1)
baldate = Application.InputBox(Prompt:="Enter date at which balance applies", Default:="17-06-01", Type:=1)
duedate = Application.InputBox(Prompt:="Enter first charge date eg usually Monday coming ", Default:="18-06-01", Type:=1)
todate = Application.InputBox(Prompt:="Enter last charge date if different from end of year ", Default:="31-03-02", Type:=1)
rent = Application.InputBox(Prompt:="Enter weekly rent", Default:="45.00", Type:=1)
ActiveSheet.Cells(13, 1).Value = "Ref" & rentac
ActiveSheet.Cells(81, 2).Value = rentac
ActiveSheet.Cells(17, 1).Value = "Dear " & tenant
ActiveSheet.Cells(51, 1).Value = tenant
ActiveSheet.Cells(52, 1).Value = adda
ActiveSheet.Cells(53, 1).Value = addb
ActiveSheet.Cells(54, 1).Value = AddEx
ActiveSheet.Cells(55, 1).Value = addc
ActiveSheet.Cells(56, 1).Value = addd
ActiveSheet.Cells(25, 2).Value = baldate
ActiveSheet.Cells(25, 8).Value = bal
ActiveSheet.Cells(27, 2).Value = duedate
ActiveSheet.Cells(27, 4).Value = todate
ActiveSheet.Cells(27, 7).Value = rent
fred = DateDiff("w", duedate, todate)
ActiveSheet.Cells(27, 5).Value = fred + 1
eric = DateDiff("m", duedate, todate)
'ActiveSheet.Cells(27, 9).Value = eric + 1
Dim boolCanExit As Boolean
Do
boolCanExit = True ' will always exit unless the user enters incorrect value
freq = Application.InputBox(Prompt:="Would you like the standing order paid monthly or weekly?", Default:="monthly", Type:=2)
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 freq = "monthly" Then
If Ms >= 8 Then
eric = eric - 1
End If
'the annual charge
wop = ActiveSheet.Cells(29, 8).Value
'the annual charge divided by how many periods are left
jazz = wop / (eric + 1)
'the rounded figure
ollie = (Round(jazz, 2) + 0.01)
'the two multiplied together which gets you within pennies of the right fig
thomas = ollie * (eric + 1)
remfig = (thomas - wop) 'the odd fig
dick = ollie - remfig
ActiveSheet.Cells(31, 1).Value = "Less 1 payment @"
ActiveSheet.Cells(31, 2).Value = dick
ActiveSheet.Cells(32, 1).Value = (("Less " & eric) & " payments @")
ActiveSheet.Cells(32, 2).Value = ollie
ActiveSheet.Cells(32, 8).Value = eric * ollie
'this bit sets the payment date code
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
Msg = Msg + 1
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(88, 3).Value = instal & mon
ActiveSheet.Cells(88, 4).Value = "monthly"
ActiveSheet.Cells(89, 3).Value = "15-03-02"
Else
If freq = "weekly" Then
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(88, 4).Value = "weekly"
ActiveSheet.Cells(89, 3).Value = "25-03-02"
'MsgBox "Next Monday will be " & datTemp
End If
Next
Dim datnex As Date
Dim dated As Date
dated = datTemp
For i = 1 To 7
datnex = DateAdd("d", i, dated)
If Weekday(datnex) = vbMonday Then
ActiveSheet.Cells(88, 3).Value = datnex
'MsgBox "Next Monday will be " & datnex
End If
Next
Dim derrick
derrick = DateDiff("w", datTemp, todate)
'ActiveSheet.Cells(31, 2).Value = (ActiveSheet.Cells(29, 8).Value) / (derrick + 1)
' ActiveSheet.Cells(31, 1).Value = (("Less " & derrick + 1) & " payments @")
'ActiveSheet.Cells(31, 8).Value = (ActiveSheet.Cells(31, 2).Value) * (derrick + 1)
'the annual charge
wop = ActiveSheet.Cells(29, 8).Value
'the annual charge divided by how many periods are left
jazz = wop / (derrick + 1)
'the rounded figure
ollie = (Round(jazz, 2) + 0.01)
'the two multiplied together which gets you within pennies of the right fig
thomas = ollie * (derrick + 1)
remfig = (thomas - wop) 'the odd fig
dick = ollie - remfig
ActiveSheet.Cells(31, 1).Value = "Less 1 payment @"
ActiveSheet.Cells(31, 2).Value = dick
ActiveSheet.Cells(32, 1).Value = (("Less " & derrick) & " payments @")
ActiveSheet.Cells(32, 2).Value = ollie
ActiveSheet.Cells(32, 8).Value = derrick * ollie
Else: MsgBox "Error you have not entered a relevant frequency Please enter monthly or weekly"
boolCanExit = False
End If
End If
Loop Until boolCanExit = True
End Sub
Many thanks
|
|
|
|
|
i'm looking for a vba code for send mail with attach
|
|
|
|
|
I am looking for an editor for VB that would replace the existing clunky one. Ideally this editor would store bookmarks and have more advanced search capabilities.
|
|
|
|
|
The problem is that it would be a real pain to reproduce the behaviors of the dropdowns n' such...
 However it wouldn't be hard for you to code an ad-in thing-IE (pun intended) to handle that as a floating_window/toolbar/menu using a custom CommandBar or two... perhaps also utilizing the WebBrowser control. The key is to check out what you get a grip on when you create a new 'add-in' project, by looking in the object browser.
CodeCrafty®
nauhaus@swbell.net
|
|
|
|
|
Can anybody give me some sample coding in VB6 to get the row number of a selected row in a DBGrid.
|
|
|
|
|
How do I determine how many pages in a tif image file.
|
|
|
|
|
hi!
I want someone who'll help me with a pivot table and pivot chart in vb
when i'll press double click on the chart i'll get then next level from the coulmn in the pivot.
thanks!
|
|
|
|
|
I have an MTS COM component that is used for updating a SQLServer database. The component sits on the SQLServer computer, whereas the client of the component sits on various user machines (the typical 3 tier setup). I have written 3 events in the component. In the client app, all works fine until I add 'WithEvents' to the variable declaration for the object. When I run the client application, I get a 'Permission Denied' error. What's up? The SQLServer PC is running Windows 2000 Advanced Server, and the client app and COM component were compiled with VB6, SR4.
Dim U As tldll.Update 'This works fine, but...
Dim WithEvents U As tldll.Update 'fails with the Permission Denied error
Thanks in advance for any ideas.
Jamie Nordmeyer
Portland, Oregon, USA
|
|
|
|
|
I don't know what the problem might be, but one thing I can tell you is that your design is simply disastrous for n-tier applications. "WithEvents" is just not meant to be used that way.
Look at the COM+ event system or redesign your app using interface callbacks or something like that.
___________
Klaus
[www.vbbox.com]
|
|
|
|
|
Why I need the UDP ?
i want to create something (Like OCX or DLL) to let the server send a message to the client(s) inform them to refresh ,redirect ,there is new email, XXX is logged on.
how i'm thinking to solve this problem?
create client OCX (without using the WinSock.OCX) to wait and manage the server message. and create an DLL on the server that will send the message to the Client OCX. (i think this is a good solution ?! do you think that ????!! )
What i want ?
i do not know how to write a code deal with UDP ?!
if you have another idea ?? i'll be pleased to you if you send it to me
Note : I’m talking about intranet.
Can any one there help me ? or give and any article(s) that could help ?!  
web programmer.
|
|
|
|
|
What you're describing here is basically an NT service, and has nothing to do with UDP. You can do all this using TCP, named pipes or even mailslots. However, I would discourage anyone from trying to write a service in VB as it's probably more pain than not.
___________
Klaus
[www.vbbox.com]
|
|
|
|
|
(Sorry, I'm cross posting this to both the VB and C++ forums as it covers both).
I have a common problem - I need to show the modeless forms which reside in a VB ActiveX Dll, called from an MFC client app.
I know that it isn't possible to show a modeless form in a VB ActiveX DLL (unless VB itself is also the client), because the modeless form needs to hook into a message loop to receive windows messages. The MSDN docs state that VB looks for something called the 'component manager' on the client to hook into which MFC doesn't provide.
Has anyone worked out how to get around this? Is is possible to mimic the functionality of the component manager in the client app, or at least the parts the modeless form is looking for? What is the myterious interace the VB is looking for? Am I on a hiding to nothing here?
Thanks
|
|
|
|
|
Does anyone know how I might extract sets of duplicates from an excel spreadsheet and then insert extra rows around them?
I have an excel spreadhsheet with sets of rows with one cell that may be the same account no as another cell. When I sort them I have say sets of three or four or five rows all with one cell the same. Eg cell d is a unique account no ref. So I might have a number of the same account no in the sheet and on sorting they are all together. Next I would like to put a blank row above and below the duplicate group , then I would like to auto sum one of their columns for each set of duplicates. In this way I can get a net balance for lots of sets of the same ref no.
Anyone any ideas?
thanks
|
|
|
|
|
Hi All.
I´ve create a dual interface in VC++ with this method "void SegundoError([out] BSTR my_string)" in the events interface and with its corresponding Fire in the CProxi class. In VB I´ve capture this event: "MiInterface.SegundoError(ByVal string_from_VC As String" and the problem is that "string_from_VC" arrives empty. Cuold tell me someone what I´m doing wrong?
Thanks![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Why did you define your string as [out]?
Who is providing the string VB or VC?
* if your string is defined in VB and you want to view it in VC, use [in]
* if your string is returned to VB, then you should use [out] BSTR * pbstrOut
Can you give me more details, please!
Did you create your VC++ object using ATL?
How do you create an instance inside VB?
What is the code that you have in your VC++ function?
You should realize that BSTRs are not like LPTSTR or TCHAR *
so, inside your SegundoError:
SegundoError(BSTR my_string)
{
USESCONVERSION;
LPCTSTR psz = W2T(my_string);
printf("String is : %s", psz);
}
Mh2!
|
|
|
|
|
If you are trying to pass a string out from VC to VB using a connection point, the IDL should be
[in]BSTR my_string
Michael 
|
|
|
|
|
Your method is "void SegundoError([out] BSTR my_string)"
[out] parameters must always be pointers, so your method should be
method: "void SegundoError([out] BSTR *my_string)"
Hope it helps! 
|
|
|
|
|
Hi:
I´ve define a dual interface with connection points in VC++, and I want to send data to VB, but I don´t know what have to put in VB to finish the connection. Cuold someone help me? I would grateful if you would send me an example code. Thanks.
|
|
|
|
|
I am writing a program to customize your desktop using the Windows Plus! Settings. The only way I could find to do so is to change the registry settings manually and then do a ChangeDisplaySettings with CDS_UPDATEREGISTRY as a flag.
This 'tricks' the settings into being active. There's got to be a better way to do this! Anyone know how to change these settings more efficiently? Does there exist an API that allows you to refresh your display without making a "ChangeDisplaySettings" type change?
OS: Windows NT
Thanks!
|
|
|
|
|
I have a MDI application, I like to know if I have to use DIB to display bitmaps..??
or is there another way?
Thank you
Ehsan
Ehsan Behboudi
|
|
|
|
|
If you mean you're trying to place a bitmap on the MDI parent, then you need to place a picture box first and then load the bitmap into it!
|
|
|
|
|
Does anyone know how to enable multiple lines in an inputbox please?
I'd like to ask the user to input an address and then put it into everal lines of an excel worksheet as you'd expect to see an address on an address label.
eg
Street
Town
COunty
PostCode
CHeers
Ian
|
|
|
|
|
Create your input box on the form. select the input box, go to the properties window for the control and scroll down the alphabetical list until you find MultiLine and set the value to True.
Cheers,
-Erik
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
My thoughts are my own and reflect on no other.
|
|
|
|
|
Trouble is I am using pop up boxes for user input that appear in a defined sequence.
This is the code I am using
accno = Application.InputBox(Prompt:="Enter account number", Default:="1234567", Type:=1)
Is it possible to do the same in another way and still guide the user through the form?
thanks
|
|
|
|
|
Use the same form (or instances of the same form). Forms are classes and can be created just like anything else. You can have an array of VB.Form object instances if you want.
___________
Klaus
[www.vbbox.com]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
