VB/EXCEL HELP

USERFORMS/CALENDAR

Page 1 of 1

10 Replies - 1863 Views - Last Post: 22 August 2008 - 03:09 AM Rate Topic: -----

#1 ALVINCHAAND  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 17-August 08

VB/EXCEL HELP

Posted 17 August 2008 - 11:18 AM

Help required. thank you in advance for any help provided

In a form where the user is supposed to enter a date, the user can
click on a link, which pops up a small calendar within that page (not
a popup window) so that the user can select a date. After the user
clicks on a date, the calendar disappears and the form is filled with
the selected date.How do i acheive this in the Userform?

sorry my print screen aint working...so cant attach my userform to show but basically what my form does is asks user for input of the following:
DATE
ITEM( COMBO BOX WITH MAINTENANCE, RENTAL, DONATIONS ETC)
REMARKS (TO ADD MORE DETAIL OF THE CASH SPENT)
DEPOSITS
WITHDRAWALS
BALANCE

CALCULATE AND UPDATE BUTTONS

Is This A Good Question/Topic? 0
  • +

Replies To: VB/EXCEL HELP

#2 dineeshd  Icon User is offline

  • member icon

Reputation: 38
  • View blog
  • Posts: 619
  • Joined: 30-June 08

Re: VB/EXCEL HELP

Posted 18 August 2008 - 01:58 AM

You can achieve this by using datetimepicker control. Add reference to Microsoft Windows Common Controls 6.0 (SP6), you can find the control there.
Was This Post Helpful? 0
  • +
  • -

#3 ALVINCHAAND  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 17-August 08

Re: VB/EXCEL HELP

Posted 18 August 2008 - 02:26 AM

hello
thank you for the fast response. problem is i am just a beginner in VB. I dont quite understand what i am supposed to do.could u pls elaborate more? how do i add a reference? sorry for the inconveniences.thank you
Was This Post Helpful? 0
  • +
  • -

#4 dineeshd  Icon User is offline

  • member icon

Reputation: 38
  • View blog
  • Posts: 619
  • Joined: 30-June 08

Re: VB/EXCEL HELP

Posted 18 August 2008 - 02:52 AM

Go to Project Menu-->Components, from the list select "Microsoft Windows Common Controls 6.0" and apply. New controls will be added to toolbox.
Was This Post Helpful? 0
  • +
  • -

#5 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: VB/EXCEL HELP

Posted 18 August 2008 - 06:11 PM

right click on the toolbar click components
Was This Post Helpful? 0
  • +
  • -

#6 dineeshd  Icon User is offline

  • member icon

Reputation: 38
  • View blog
  • Posts: 619
  • Joined: 30-June 08

Re: VB/EXCEL HELP

Posted 19 August 2008 - 12:15 AM

View Postthava, on 19 Aug, 2008 - 06:41 AM, said:

right click on the toolbar click components



Yes you are right, components can be accessed from toolbar too.
Was This Post Helpful? 0
  • +
  • -

#7 ALVINCHAAND  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 17-August 08

Re: VB/EXCEL HELP

Posted 21 August 2008 - 06:32 AM

View Postdineeshd, on 18 Aug, 2008 - 02:52 AM, said:

Go to Project Menu-->Components, from the list select "Microsoft Windows Common Controls 6.0" and apply. New controls will be added to toolbox.


Thank you i finally got my calendar to work....
Now for another question:

in my userform, user will be able to enter a deposit amt and a withdrawal amt...
i need to be able to compute the balance.... also must be able to add previous balance to this amt...
how can i go abt doing so?
i know that i m supposed to take the value the user enters by doing a deposit.val-withdrawal.val=currentbal
this balance must be added to the previous balance to give the user the updatedbalance. how do i call the previous balance value from the excel sheet?

View PostALVINCHAAND, on 21 Aug, 2008 - 06:29 AM, said:

View Postdineeshd, on 18 Aug, 2008 - 02:52 AM, said:

Go to Project Menu-->Components, from the list select "Microsoft Windows Common Controls 6.0" and apply. New controls will be added to toolbox.


Thank you i finally got my calendar to work....
Now for another question:

in my userform, user will be able to enter a deposit amt and a withdrawal amt...
i need to be able to compute the balance.... also must be able to add previous balance to this amt...
how can i go abt doing so?
i know that i m supposed to take the value the user enters by doing a deposit.val-withdrawal.val=currentbal
this balance must be added to the previous balance to give the user the updatedbalance. how do i call the previous balance value from the excel sheet?

Private Sub CMD_CANCEL_Click()
Unload Me
End Sub

Private Sub CMD_CALCULATE_Click()

'DEPOSITS -WITHDRAWAL = currentBalanceAmt then need to add this balane amt to the previous to get the most uptodate balance to display in the userform and in the excel spreadsheet db

End Sub

Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

Private Sub ITEM_COMBO_Change()
ITEM_COMBO.DropDown
End Sub
Private Sub CMD_UPDATE_Click()

Dim iRow As Long
Dim WS As Worksheet
Set WS = Worksheets("CASH_FLOW08") 'CHANGE TO SHEET NAME THAT U WISH UR UPDATE TO APPEAR IN


'find first empty row in database
iRow = WS.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row


'copy the entered data to the database
WS.Cells(iRow, 1).Value = Me.DTPicker1.Value
WS.Cells(iRow, 2).Value = Me.ITEM_COMBO.Value
WS.Cells(iRow, 3).Value = Me.REMARKS.Value
WS.Cells(iRow, 4).Value = Me.DEPOSITS.Value
WS.Cells(iRow, 5).Value = Me.WITHDRAWAL.Value

'clear the data after first set of data is entered in db so that user can enter next set
'Me.DTPicker1.Value
Me.ITEM_COMBO.Value = ""
Me.REMARKS.Value = ""
Me.DEPOSITS.Value = ""
Me.WITHDRAWAL.Value = ""
Me.DTPicker1.SetFocus

End Sub

Was This Post Helpful? 0
  • +
  • -

#8 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: VB/EXCEL HELP

Posted 21 August 2008 - 08:27 AM

put a opening balance variable and use that to store the pervious balance
Was This Post Helpful? 0
  • +
  • -

#9 ALVINCHAAND  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 17-August 08

Re: VB/EXCEL HELP

Posted 21 August 2008 - 11:17 AM

View Postthava, on 21 Aug, 2008 - 08:27 AM, said:

put a opening balance variable and use that to store the pervious balance

Thank you for the quick response. IS this the way of doing it?
Also this is incomplete...i need to call the previous balance value from the database and add it to the curBal.
How do i do that?

How do i assign the curBal to be displayed in the Available Balance(Av_BAL) Textbox when the calculate button is pressed?

[code]
Private Sub CMD_CALCULATE_Click()

Option Explicit

Dim curBal As Currency
Dim awithdrawal As Currency
Dim adeposit As Currency
Dim Av_BAL As Currency

awithdrawal = WITHDRAWAL.Text
adeposit = DEPOSITS.Text

curBal = DEPOSITS.Text - WITHDRAWAL.Text
'but this is incomplete...i need to call the previous balance value from the database and add it to the curBal.
How do i do that?

'How do i assign the curBal to be displayed in the Available Balance(Av_BAL) Textbox when the calculate button is pressed?

End Sub
Was This Post Helpful? 0
  • +
  • -

#10 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: VB/EXCEL HELP

Posted 21 August 2008 - 06:01 PM

could you explain your database structure
and which fields to store deposit, with drawal erc..
Was This Post Helpful? 0
  • +
  • -

#11 ALVINCHAAND  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 17-August 08

Re: VB/EXCEL HELP

Posted 22 August 2008 - 03:09 AM

View Postthava, on 21 Aug, 2008 - 06:01 PM, said:

could you explain your database structure
and which fields to store deposit, with drawal erc..

hi there :)

my database is very simple

just the following columns

Date (from the datepicker)
item (selected frm the combobox values in userform)
remarks (textbox for user to input additional info)
deposits (this field can be left blank if no deposits made or a value can be entered)
withdrawals
balance (still figuring out the formula for this... )
came up with =SUM(CELL("contents",F5)-CELL("contents",E6))+CELL("contents",D6)
where balance=f5, withdrawal=e6, deposits=d6

cuz i need to have the previous (balance-withdrawal)+deposit to appear in the next row when its updated but it doesnt seem to work too well...any sugestions on this?)

on the userform, i want the latest balance to be computed and displayed.how do i do that?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1