Place code in a new workbook with a macro.

H

help_wanted

First, I just want to say thanks to all the people contributin
knowlledge in this forum. It has been a weath of information for m
over the past few days. Now my question.


I have created a macro wich I have saved as an add-in in order t
distribute. This macro creates a combo box on seperate workbook an
populates it with data from the saved .xla. It also has a linke
cell on the new workbook.

My problem is that when the value in the combo box and linked cel
changes I would like another cell's value to change.

I have tried to do this with If / Then statements but of course thos
statements only get run once when the macro runs.

Idealy I would like the If / Then statements to be placed under
Private Sub ComboBox1_Change() in the new workbook. My problem her
is that the workbook is overwritten everytime I run the reporting ap
that creates the new workbook. (the reason I saved this macro as a
add-in in the first place.)

Is there anyway to do what I am attempting ??

This is the code I have in the .xla so far. Mostly thanks to yo
guys.


Columns("A:A").ColumnWidth = 16
Columns("B:B").ColumnWidth = 30
Columns("C:C").ColumnWidth = 18
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1"
Link:=False, _
DisplayAsIcon:=False, Left:=395.25, Top:=11.8, Width:=96
Height:=27.75 _
).Select

ActiveSheet.Shapes("ComboBox1").Select
Selection.ShapeRange.ScaleWidth 1.01, msoFalse
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.5, msoFalse
msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 1.02, msoFalse
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.2, msoFalse
msoScaleFromTopLeft

ThisWorkbook.Sheets("Sheet2").Range("b1").Value = "William Day"

ThisWorkbook.Sheets("Sheet2").Range("b2").Value = "Michael Becker"

ThisWorkbook.Sheets("Sheet2").Range("b3").Value = "Jason Craig"

ThisWorkbook.Sheets("Sheet2").Range("b4").Value = "Saeed Mirza"

ThisWorkbook.Sheets("Sheet2").Range("b5").Value = "Chris Webber"

ThisWorkbook.Sheets("Sheet2").Range("b6").Value = "Matthew Gray"

ThisWorkbook.Sheets("Sheet2").Range("b7").Value = "James Pence"

ThisWorkbook.Sheets("Sheet2").Range("b8").Value = "Chuc
Patenaude"

ThisWorkbook.Sheets("Sheet2").Range("b9").Value = "Jimmy Zheng"

ThisWorkbook.Sheets("Sheet2").Range("b10").Value = "Armando Ruiz"

ActiveSheet.ComboBox1.ListFillRange
"[ColWidAlgnTextTest.xla]Sheet2!b1:b10"
ActiveSheet.ComboBox1.LinkedCell = "e2"

If ActiveSheet.Range("e2").Value = "William Day" Then
ActiveSheet.Range("f2").Value = "DAYWI"
End If
If ActiveSheet.Range("e2").Value = "Michael Becker" Then
ActiveSheet.Range("f2").Value = "BECKERMI"
End If
If ActiveSheet.Range("e2").Value = "Jason Craig" Then
ActiveSheet.Range("f2").Value = "CRAIGJA"
End If
If ActiveSheet.Range("e2").Value = "Saeed Mirza" Then
ActiveSheet.Range("f2").Value = "MIRZASA"
End If
If ActiveSheet.Range("e2").Value = "Chris Webber" Then
ActiveSheet.Range("f2").Value = "WEBBERCH"
End If

ActiveSheet.Range("g6").Formula = "=COUNTIF(a1:a5000,f2)"
ActiveSheet.Range("g7").Formula = "=COUNTIF(a1:a5000,e2)"
ActiveSheet.Range("g9").Formula = "=SUM(g6:g7)"

Columns("G:G").ColumnWidth = 3.43

ActiveSheet.Range("h6").Value = "HD & CHG Tickets"
ActiveSheet.Range("h7").Value = "TSK Tickets"
ActiveSheet.Range("h9").Value = "Total Tickets Resolved"
Columns("H:H").EntireColumn.AutoFit

ActiveSheet.Range("a1").Selec
 
F

Frank Kabel

Hi
not quite sure but maybe the following site will help you:
http://www.cpearson.com/excel/vbe.htm

--
Regards
Frank Kabel
Frankfurt, Germany

First, I just want to say thanks to all the people contributing
knowlledge in this forum. It has been a weath of information for me
over the past few days. Now my question.


I have created a macro wich I have saved as an add-in in order to
distribute. This macro creates a combo box on seperate workbook and
populates it with data from the saved .xla. It also has a linked
cell on the new workbook.

My problem is that when the value in the combo box and linked cell
changes I would like another cell's value to change.

I have tried to do this with If / Then statements but of course those
statements only get run once when the macro runs.

Idealy I would like the If / Then statements to be placed under
Private Sub ComboBox1_Change() in the new workbook. My problem
here is that the workbook is overwritten everytime I run the
reporting app that creates the new workbook. (the reason I saved this
macro as an add-in in the first place.)

Is there anyway to do what I am attempting ??

This is the code I have in the .xla so far. Mostly thanks to you
guys.


Columns("A:A").ColumnWidth = 16
Columns("B:B").ColumnWidth = 30
Columns("C:C").ColumnWidth = 18
Columns("B:B").Select
With Selection
HorizontalAlignment = xlCenter
VerticalAlignment = xlBottom
WrapText = False
Orientation = 0
AddIndent = False
ShrinkToFit = False
MergeCells = False
End With

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1",
Link:=False, _
DisplayAsIcon:=False, Left:=395.25, Top:=11.8, Width:=96,
Height:=27.75 _
).Select

ActiveSheet.Shapes("ComboBox1").Select
Selection.ShapeRange.ScaleWidth 1.01, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.5, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 1.02, msoFalse,
msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.2, msoFalse,
msoScaleFromTopLeft

ThisWorkbook.Sheets("Sheet2").Range("b1").Value = "William Day"

ThisWorkbook.Sheets("Sheet2").Range("b2").Value = "Michael Becker"

ThisWorkbook.Sheets("Sheet2").Range("b3").Value = "Jason Craig"

ThisWorkbook.Sheets("Sheet2").Range("b4").Value = "Saeed Mirza"

ThisWorkbook.Sheets("Sheet2").Range("b5").Value = "Chris Webber"

ThisWorkbook.Sheets("Sheet2").Range("b6").Value = "Matthew Gray"

ThisWorkbook.Sheets("Sheet2").Range("b7").Value = "James Pence"

ThisWorkbook.Sheets("Sheet2").Range("b8").Value = "Chuck
Patenaude"

ThisWorkbook.Sheets("Sheet2").Range("b9").Value = "Jimmy Zheng"

ThisWorkbook.Sheets("Sheet2").Range("b10").Value = "Armando Ruiz"

ActiveSheet.ComboBox1.ListFillRange =
"[ColWidAlgnTextTest.xla]Sheet2!b1:b10"
ActiveSheet.ComboBox1.LinkedCell = "e2"

If ActiveSheet.Range("e2").Value = "William Day" Then
ActiveSheet.Range("f2").Value = "DAYWI"
End If
If ActiveSheet.Range("e2").Value = "Michael Becker" Then
ActiveSheet.Range("f2").Value = "BECKERMI"
End If
If ActiveSheet.Range("e2").Value = "Jason Craig" Then
ActiveSheet.Range("f2").Value = "CRAIGJA"
End If
If ActiveSheet.Range("e2").Value = "Saeed Mirza" Then
ActiveSheet.Range("f2").Value = "MIRZASA"
End If
If ActiveSheet.Range("e2").Value = "Chris Webber" Then
ActiveSheet.Range("f2").Value = "WEBBERCH"
End If

ActiveSheet.Range("g6").Formula = "=COUNTIF(a1:a5000,f2)"
ActiveSheet.Range("g7").Formula = "=COUNTIF(a1:a5000,e2)"
ActiveSheet.Range("g9").Formula = "=SUM(g6:g7)"

Columns("G:G").ColumnWidth = 3.43

ActiveSheet.Range("h6").Value = "HD & CHG Tickets"
ActiveSheet.Range("h7").Value = "TSK Tickets"
ActiveSheet.Range("h9").Value = "Total Tickets Resolved"
Columns("H:H").EntireColumn.AutoFit

ActiveSheet.Range("a1").Select
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excell to Web Form filling macro 0
Pull Data From an Add-in to a Workbook 2
Merge data from two temp files into a master 1
If/Elseif AutoFilter Macro 4
Insert Picture using Macro 10
skip actions within a macro if a cell value is zero 1
Excel2000 VBA: How force the procedure to wait until queries are refreshed? 2
Find a value in a sheet and replace it with another value if it matches 8

Top