Forms

Resolved · Medium Priority · Version 2010

Wed 1 Feb 2012, 12:36 Delegate Ross said...

Ross has attended:
Excel VBA Intro Intermediate course

Forms

I have created a form

Sub Selectionlist()

Account.AddItem "Net Income1"
Account.AddItem "cash1"
Account.AddItem "Net Income1"
Account.AddItem "Depreciation & Amortisation"
Account.AddItem "Intercompany Interest"
Account.AddItem "Minority interest"
End Sub

Then i have addeded a command button

what i would like to do is

once they have selected one of the items in the drop down on fhe form do a function. say

if "selections list above" = "Net Income1" then
Range("a1:b2").select.
End If

The problem i have is cant get the macro to refer to the form. I need to name the selection in the form somehow?

Also how would the command button know what macro to run(will it just run the routine sitting behind the form? if that the case i guess i could call other macros into the routine?)

Fri 3 Feb 2012, 11:48 Trainer Simon said...

RE: Forms

Hi Ross,

Thank you for your question.

The value you talk about are they displayed in Text Boxes or a combo box? If so, name the text boxes and combo box in the properties in the VBA window.

Double click into the form VBA and create a new routine. The code would be something like this:

e.g. cboTest is the name of the combo box

Private Sub cboTest_change()

Select Case Me.cboTest

Case Is = "T1"
Sheets("Sheet1").Range("a1:a5").Select

Case Is = "T2"
Sheets("Sheet1").Range("b1:b5").Select

Case Is = "T3"
Sheets("Sheet1").Range("c1:c5").Select

Case Else

Sheets("Sheet1").Range("d1:d5").Select

End Select

End sub

Add a Command button to sheet1 and right click view code. In the command button procedure type the name of the form followed by .show

e.g. MyForm.show

I hope this helps.

Regards

Simon

Fri 10 Feb 2012: Automatically marked as resolved.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Access tip:

Calculating The Difference Between Dates

If you wish to calculate the time between two date fields, this can be done in a number of ways:

1. As a calculated field in a query
2. As a calculated control in a form or report
3. As a calculation in a VBA procedure.

The basic syntax to get the number of days between two dates is:

=[One Date Field] - [Another Date Field]

You can also use one of the following functions:

=Month([One Date Field] - [Another Date Field])
which calculates the number of months between the two fields

=Year([One Date Field] - [Another Date Field])
which calculates the number of years between the two fields.

Another function is the DateDiff() function.

It uses an argument to determine how the time interval is measured. For example:

=DateDiff("q",[One Date Field] - [Another Date Field])
returns the number of quarters between the two fields.

Other intervals that can be used in this expression are as follows:
"yyyy" - Years
"m" - Months
"d" - Days
"w" - Weekdays
"ww" - Weeks
"h" - Hours
"n" - Minutes
"s" - Seconds