a small project

[jiemei1]

I need to develope a program that use ActiveX control on the first workbook to gather year and two different product code, then return the price variance in a text box on the first workbook.

there are several workbooks in one excel file.
the first workbook contains all the ActivX control, remaining workbooks have name 1999,2000, 2001, ...2006 which stands for year 1999 to 2006.
each work book contains same type of information: product code and price.

first time VBA user. please help!


Thanks,
Jie

 

[SkipVought]

Hi,

"there are several workbooks in one excel file."

Do you mean that there are several WorkSheets in one Workbook, which is an Excel file?

Have you written ANY code yet? Please post where you are running into problems This is Tek-[red]Tips[/red], not Tek-[red]we-write-the-code-for-your-project[/red].

And you have not really told us what you are doing in any detail. Where are the values coming from on the sheet? Whats on this OTHER Sheet and in what format?

Please be clear, concise and complete.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[jiemei1]

the code below is intend to look into a Combo box for the year value then go into the correspondent worksheet.

IT is in the process to install VBA on my computer so I can't test my code.
Two things I am not sure base on the code I wrote:
1. I used Combo box for info input. combo box is link to cell A1. that is why I use "A1=Year". am I correct?
2. If statement is awfully cumbersome. How can I simplify it?

Thanks

---------------------------------------------------
Sub CalculatePriceVar()
Msg1 = "You are about to calculate the product price difference. Do you want to continue? If yes, Please select the correspondent year. ?"
Rt = MsgBox(Msg1, vbYesNo, "Caution !")
If Rt = vbNo Then Exit Sub

Dim Year As Integer
A1 = Year

If Year = 1999 Then
Sheets("1999").Select
Call SUBCAL
Else
If Year = 2000 Then
Sheets("2000").Select
Call SUBCAL
Else
If Year = 2001 Then
Sheets("2001").Select
Call SUBCAL
Else
If Year = 2002 Then
Sheets("2002").Select
Call SUBCAL
Else
If Year = 2003 Then
Sheets("2003").Select
Call SUBCAL
Else
If Year = 2004 Then
Sheets("2004").Select
Call SUBCAL
Else
If Year = 2005 Then
Sheets("2005").Select
Call even1
End If


End Sub

 

[SkipVought]

If you have Excel, Word, PowerPoint -- any of the MS Office suite -- you already have VBA. There is NOTHING to install!

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[jiemei1]

Then why all my ActiveX button is not working?

For example, when I right click the Common button, I should be able to set properties, assign marcro, etc.

any where I need to reset to make them work?

Thanks,

 

[SkipVought]

Please explain how you got your ActiveX button on your sheet.

What TOOLBAR did you activate to do this?

How are you linking this to A1?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[fumei]

Are you sure you are using an ActiveX control? You got it from the Controls toolbar, correct? Not the Forms toolbar?

As for your IF statement. It would probably be cleaner to use a Select Case, rather than an IF. Select Case is better at getting multiple results for the same variable.

Select Case Year
   Case 1999
     Sheets("1999").Select
     Call SUBCAL
   Case 2000
     Sheets("2000").Select
   Case 2001
     Sheets("2001").Select
     Call SUBCAL
   Case 2002
     Sheets("2002").Select
     Call SUBCAL
   Case 2003
     Sheets("2003").Select
     Call SUBCAL
   Case 2004
     Sheets("2004").Select
     Call SUBCAL
   Case 2005
     Sheets("2005").Select
     Call even1
End Select

Actually, even the above could be tightened up and made smaller since most of the Cases make the same call. Case can take a range of number. You could take the specific range (1999, 2000 etc) and use that as a string for the Sheet. Look up Select Case in Help. Hopefully you have Help installed - note it is NOT by default.

Gerry

 

[jiemei1]

Yes, I got it from the control toolbar. just go to "View" select "Toolbars" then activated "Control Toolbox". You can choose a lot of button from there - list box, combo box, toggle botton etc.

Base on the book, I am suppose to set properties etc by right clik the bottom but I can't do it, why?

the code should read the value from A1 or from the combo box directly?


Gerry, thanks for the "select case" suggestion. will try it out.

Thanks,

 

[SkipVought]

Properties are set in the Properties button on the toolbar.

If you want the selection from the combo box in A1, double-click the button...

Private Sub ComboBox1_Change()
  [A1].value = ComboBox1.value
End Sub

BTW, you must Exit Design Mode BEFORE the control will work.


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[jiemei1]

Thank you very much!
That is exactly what I need!

Jane

 

[IknowMe]

Tek-we-write-the-code-for-your-project.

Could someone please give me this link???

Wow, i'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[fumei]

MrMilson...we've been trying to get your email address/URL for weeks. Wait a sec...it WAS you who volunteered, right?



Gerry

 

[SkipVought]

Looks like everyone else took two steps backward! ;-)

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[fumei]

Just a recursive moment.

Gerry

 

[SkipVought]

Recusive! Foiled again!

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[fumei]

Oh Skip....groan....

Someone shoot that man.



Just kidding.

Gerry

 

[SkipVought]

Caution:

If your write recursive...

you may never finish the tome.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[fumei]

Just stop now....please?

Gerry