Run A Macro Based On Cell Change

Mshflexgrid Cell Color Change Based On Pos. Neg. Numbers
I need to change my column 2 "Balance" foreColor to red if negative & green if positive.

Any ideas?

This obviously wrong. I was playing with the code:

Code:
Dim ic As Integer

With MSHFlexGrid1
    For ic = 1 To MSHFlexGrid1.Rows - 1
    If .TextMatrix(.Rows, 2) < "0" Then
        .TextMatrix(.Rows, 2) = vbRed
    Else
        .TextMatrix(.Rows, 2) = vbGreen
    End If
    
      .TextMatrix(ic, 2) = Format(.TextMatrix(ic, 2), "$#0.00") 'might need to check the format syntax I used here!
    Next ic
            
End With



Thanks,

Vwalla

---

Run A Macro On Cell Change
New at this but reasonably competent and have searched various threads & sites for 2 days and have it working but not in the proper fashion.

Using:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Application.EnableEvents = False
' Look to see if Value in Cell 1,1 changes from 0
If Target.Value >= 1 Then
' Call the Macro contained in Module1
Call UpdateSPCResults
End If
Application.EnableEvents = True
End If

End Sub

Works great if you actualy type into the cell and press Enter. The problem is that $A$1 is populated via a DDE Poke from another app based upon an event within that app and it appears that the Worksheet_Change object is blind to that type of change.

When I manually change the contents of $A$1 my macro fires fine and pulls a ton of info from the app that feeds $A$1 - just as I need it.

Have a bridge around it using a macro that just fires every 15 seconds to see if there is a change but it really is a waste of resources - and not the right way to do it.

Thoughts, direction, suggestions? Willing to do the work just don't know where else to look. Thanks

---

Run Macro On Specific Cell Change
Is there a way to have a VB Macro run when a specific cell changes value ? I am able to have a macro run when a "sheet data change" takes place, but do not know how to isolate a specific cell to be watched, while ignoring any other changes to the sheet.

Thanks in advance for any help !!!

---

Trigger Macro On Change Of Linked Cell
I have a Excel VB Macro which triggers on any change to a specific cell, however it does not trigger if the cell value is "linked". That is, if the target cell is A1, and it is linked from A2, changes to A2 do not trigger the macro. Is there a way to get the macro to look at a "linked" value of A1, and not the formula itself ? Thanks...

---

Activating (or Retrieving) The Value Of A Cell, Based On The Current Active Cell
We will test the patience for newbies!


I want to use the address of the current active cell on Sheet1 to move to the same cell on Sheet2 and then to the same column but an offset of a variable number of rows, and then use the value of that cell.


Eg the active cell is C3, an element of an nxn array on Sheet1, the upper left corner is in A1. I want to retrieve the value at the same relative location to the upper left corner pf one of several nxn arrays on Sheet2, where (if necessary) I can specify an offset after activating sheet2.[ C3 ] to Cx, where x is a calculated value.

Thanks from the newest of newbies!

---

Track Cell For Highlight Based On Cell Itself, Not Row Number
I am highlighting a cell in each row as that row is selected, and 'un'-highlighting the previous one. Right now I'm doing it based on the row number, but a problem occurs when I sort my columns. Obviously, after the sort, the row number of the highlighted cell changes and this makes the previous row and the current row the same sometimes, so it doesn't take the backcolor off when it should. Is there a way to keep track of which is highlighted by the cell itself, instead of the row number?

---

Selecting A Cell Based On Where The Active Cell Is
Firstly. Hello gurus, developers, lurkers and other bobs. It is a pleasure to find this place and meet you all.

Secondly. Here is the problemette that I am looking for assistance on.

I have been given an Excel spreadsheet to maintain and improve. What I am looking for is to be able to copy data from one worksheet to another with the following requirements. The data that I select is in worksheet 1, columns A and C which I then want to copy to worksheet 2. The process will be dependent on where the active cell is in worksheet 1, which could be anywhere within the range D3 to L1000. (I hope this makes sense)

Up until being given this spreadsheet to maintain I have never used any form of VB before. I have tried a multitude of searches here and on a few other sites and the results of those indicted using Range(ActiveCell, ActiveCell.End(xlToLeft)).Select in some way but so far I have been unable to make it function as required so I am open to suggestions.

Regards

Youngnutter

---

Select And Copy Just Cell Content (not The Cell Itself) By VB Macro
Hi,
my problem is that I can select the content of Cell in Excel sheet [by doubleclicking (or F2) onto the Cell and simply select the whole text inside] and copy it as text.
But I cannot do this by macro.

Is there any chance to select content and copy it by VB macro?

---

Cell Value Based On Cell Range
I am trying to have the value of cell A multiplied by a certain amount in Cell B, based on a value range of cell C. Any help would be appreciated.

Ex:

Cell A = 25
Cell B = the value of Cell A multiplied by 10(if the value of Cell C is less than 18), multiplied by 15(if the value of Cell C is greater than or equal to 18), or multiplied by 20(if the value of Cell C is is greater than 20)
Cell C = percentage value (IE. 20.00%)

---

Change Cell Format Only While Cell Is Selected
Hi All,

I have some code that was provide here that will find values in a worksheet, what I would like to do is when the cell is selected, have the cell change its format ie.. background colour red and font white, but then when another cell is selected have the cell previous cell return to its orginal format and the current cell formatted again as was done previously.

Regards

David

---

Change The Value In A Cell P If I Change Something In Cell O With Excel?
If value in cells P2-P7 changes, it should update the value in corresponding cells O2-O7.

It should update like this:

If P = 1 Then O = 36
If P = 2 Then O = 72
If P = 3 Then O = 101
If P = 4 Then O = 122
If P = 5 Then O = 151

How do I do that in Excel?

---

New Sheet Name Based On Cell Value.
Hi, I am new to VB in excel, and i am looking to see if there is a way to name a sheet based on a value in a cell.

I have set up a macro to copy an exising sheet with contents included, then to make this data "values" (so cannot be changed later).

I would then like this new sheet to be named by the value that is in the sheet just made ie from cell A1. If this possible and if so how could i do it?

Additionally i am also looking to create an auto number where by when i push A macro button a new number is generated on a sheet in the next row of the same column.

ie A1 = 1 A2 = 2, when button pushed A3 = 3 and so on. I am unable to find out how to do this using a macro...or a function.

As said i have most of what i need working using a macro (albeit a little slow) but cannot work out those last two points.

Any help woud be apreciated.

Regards

Fishboy

---

Separate A Cell Value Based On Being Bold?
Suppose I have this value in a cell

text = "MY Name is Cheetah"

How to separate it (maybe using mid function) into
text1 = "My Name" or "My Name" &
text2 = "is Cheetah" ?

Thanks

---

Sum Cell Values Based On Criteria
Hi,

I have an Excel spreadsheet in the format below. I'd like to write a formula that can calculate the trader commission total, as well as the broker commission within each trader. Essentially, it'll add the commission columns for each value relating to trader1 to give an accumulative. It will also add the commission totals for lloyds & hsbc for trader 1.

Then do this for the rest of the traders.
Any ideas on how to do this much appreciated.

Thanks

broker commission Trader

lloyds 3,432 trader1
lloyds 5,433 trader1
lloyds 21,231 trader1
hsbc 4,204 trader1
hsbc 16,884 trader1
hsbc 43,343 trader1
hsbc 4,563 trader1
hsbc 10,000 trader1
abbey 5,500 trader1
abbey 2,323 trader2
abbey 324,234 trader2
lloyds 5,169 trader2
lloyds 544 trader2
lloyds 5,169 trader2
hsbc 5,169 trader2
hsbc 34,343 trader2
hsbc 5,500 trader2
hsbc 5,169 trader2
hsbc 10,000 trader2
abbey 5,500 trader2
abbey 11,550 trader2
abbey 34,343 trader 3
lloyds 10,000 trader 3
lloyds 10,000 trader 3
lloyds 34,343 trader 3
hsbc 10,000 trader 3
hsbc 34,343 trader 3
northern rock 10,000 trader 3
northern rock 34,343 trader 3



Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

---

Vba - Increasing Cell Value Based On Date
everytime i have a problem with vb, it always gets solved at vbcity, so here i am again with another problem.

ok, so here it is:

i have some cells with multiple values in them, and on the 1st of every month, i want interest to be added at 1/2 %. and other than getting the current date, im not sure what to do.

i realise its vba problem, but its quite a newbie problem so i posted it here.

any help is much appreciated.

Primary_Slave

---

Finding/Editing Cell Based Upon VLookUp
Here's my situation:

I have a form on one worksheet for filling out purchase invoices, and I have another worksheet that has my entire inventory on it. I have 4 columns on the inventory page: Item Number, Description, Price, and In Stock. When they click Submit on the Invoice Worksheet, I want it to take the value of a text box [which will be an item number], find that row on the Inventory Worksheet, and subtract 1 from the In Stock column.

Does that make sense? Is this possible? I'm completely stuck as to how to accomplish this. Thanks in advance for any and all help - it's greatly appreciated!

-Tony

---

Cell Contents Based On Date - Are VBA And VB6 Similar?
Hi All,

I am quite a competent vb6 programmer. I only really dable is the small programs. I have never programmed using vba though. Are the languages of vba and vb6 similar?

I am trying to program a vb macro that will alter the contents of some cells on launch. Basically one column has the purchase dates of some items and the next column has the age status. If the item is under 3 years it should say green, 3 - 5 years = amber, over 5 years = red.

I can't quite get my head around the intergration bit though. Are there are resources that you would recommend me reading?

Thanks
Your Humble
SpaceMonkey

---

Select Range Based On Cell Content
Hi there

I am new to VBA and am trying to write some code to select a range of cells dependent on the content and copy and paste it into another worksheet.

I have attached a sample file so that it is easier to see what i want to do. Basically, i want to select all the cells in column A that contain ADE, for example, and then for those copy all the relative cells in columns B, D, E and L.

The problem i have is that there will be a different number of ADE's each time i create the spreadsheet, and the first occurance of ADE will not always be in cell A4, so i need the code to be able to search the sheet for the first occurance and select the relevant number to copy.

Can anyone help?

---

Color FlexGrid Based On Data In Cell
I have a column and I want to paint it certain colors depending on the text.

I found some code and tried to recycle it but that didnt work... Here is the code

VB Code:
Dim lrow, lcol As Integer    Dim lcolor As String            With MSFlexGrid1                For lrow = .FixedRows To .Rows                    .Row = lrow                    If UCase(.TextMatrix(lrow, 1)) = "BREAK" Then                        lcolor = vbGreen                        For lcol = 0 To 1                            .Col = lcol                            .CellBackColor = lcolor                        Next lcol                    ElseIf .CellBackColor <> vbGreen Then                        lcolor = vbRed                    End If                Next lrow        End With


It goes into an infinity loop and crashes the IDE.

---

Changing Labels Based On Cell In FlexGrid
I'm trying to change the color of these labels that I created on the outside of a grid.
Depending on which cell they choose, it corresponds to a Month & Day. Since the grid is 12x31, there are many cells and it may be more user-friendly for me to highlight the corresponding day associated to that cell.

THE PROBLEM:
For every cell that I click, the label will be highlighted. I need to some how reset the labels to default once I click on a different date.

Code:
Private Sub MSFlexGridVacationDetails_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
Month = MSFlexGridVacationDetails.Row
Day = MSFlexGridVacationDetails.Col

    
    txtMonths(Month).ForeColor = vbHighlightText
    txtDays(Day).ForeColor = vbHighlightText
End Sub

Thanks!

---

Charts Based On Series And Ranges Held In A Cell
Hi I would like to export charts to folders in a html project.

I have the direct path for the server and the destination for all of the files and I can send the files to the folders where they will already have a reference and will show in the webpage once exported.

I know how to use the code to say the path, the filename and export the chart but I have a lot of charts to export and I would like to be able to add to the charts in a sheet holding the criteria for the charts to be based on.

These may be dynamic ranges on sheets holding expanding rows because of the date change. They may just be column references to tie in to the chart for the other axis.

Is this possible?

Thanks
Rob

---

Sending Rows From One Sheet To Another Based On Cell Value In Column A
Hi everyone

I've been racking my brains since yesterday to come up with an answer to this but to no avail so am hoping that one of you lovely people can help me out.

What it is, I have a worksheet, "Data", that contains 7 columns, and lots of rows (will be different each time report is run). I also have five other worksheets, Revenues, Benefits, etc etc.

Column A in the Data sheet are Team Managers names. Each team manger is from a specific service. What i want to do (by poss a comand button on each of the other worksheets?), is to copy the the rows from the data sheet based on whether the team manager relates to that service, i.e.

1 Joe Bloggs
2 Joe Bloggs
3 Bob Smith
4 Andy Jones
5 Andy Jones

So if Joe Bloggs is on revenues, then I want rows 1 and 2 to be copied to the sheet Revenues, Bob Smith is on benefits so I want row 3 to be copied to the worksheet Benefits. Then Andy Jones is also on Revenues so I want rows 4 and 5 to be copied to the Revenues sheet, being careful not to overwrite the rows that have already been copied there.

I know you're probably thinking why can't this be done by copying and pasting but there is sometimes in excess of 2000 rows and this would take a very long time to do.

Any help on this and I would be extremely grateful.

Cheers
Lindsey

---

Hide Adjacent Cells Based On A Single Cell Value
I am trying to hide a row of cells (not the entire row)based on it's adjacent value of a single cell. For example we are evaluating the following cells individually:
AA42:AA61

if AA42=0 the we need to hide cells N42:Z42 using the ;;; format.
Below is some code I came up with (thanks to this wonderful forum). I am almost there but need to hide cells not an entire row.

Thanks!

Sub HideCells()

'Sub to hide cells in adjacent range if = 0
'******************************
Range("AA42:AA61").Select
For Each cell In Range("AA41:AA42")

If cell.Value = "0" Then
cell.EntireColumn.Select
With Selection
.EntireColumn.Hidden = True
End With
End If
If cell.Value > "0" Then
cell.EntireColumn.Select
With Selection
.EntireColumn.Hidden = False
End With
End If
Next cell


End Sub

---

MSFlexGrid Column Width Based On Cell Text
How can I automatically set the column width of a column in an MSFlexGrid to be the same size as the text in the currently selected cell. I want the cell to show the entire text.

-Mark

---

Copy Cell Data In One Sheet To Another Based On Condition?
Hi, I am a VBA newbie and I need your help.

What I want to do is copy a cell value in sheet 1 based on a date in sheet2 and paste that value into sheet2. The cell in sheet2 where the date is will always be B2. In sheet1 the dates are in row A. The data i want to get is in row g of sheet1. I need to do this for 100 xls files in a directory. How can I go about doing this?

Thanks in advance.

---

Need Help Using Macro To Input Data Into Web Based Server
first of all, i have very little VB knowledge. My problem is, a friend of mine said he could help me with a mindless repetitive job i have at work by using the macro. The macro basically inputs names into a webpage, loads the next page, inputs a number into a field, tabs to the next field, inputs a second numbher, then loads the next page then starts all over again. I am typing the names into excel and then having the macro copy and paste them onto the web page once for each loop (the numbers are associated with the name but simply increase by increments of 1 each time). My huge problem is that i would like for the macro to run itself, but i dont know how to tell the macro to wait until the web page has loaded. My friend who got me this far is on vacation and i just found out that my supervisor wants this program by tomorrow. Can anyone please help me with any ideas? keep in mind that i have very little VB knowledge so you'll have to explain any code you send me. thanks

---

Can I Execute Macro In Access Based On Time
ok.. i have an access macro that runs every thursday when i leave work.. the probem is that i like to leave at four while others stay until five... i need to wait for everyone to be finished working before running the macros... is there a way to set the macro to run at a certain time... (7:00pm)
thanks for the info
shawn

---

Open Webpage In Macro Based On Date.
I have a macro project that I'm creating in which excel opens a webpage. The webpage address is the following:

http://webapps.3abn.org/program/ViewWebSchedule.cfm?StartDate=11/01/2005&EndDate=11/30/2005&Net=1

In my macro I have the following command:


Code:
 Workbooks.Open Filename:= _
        "http://webapps.3abn.org/program/ViewWebSchedule.cfm?StartDate=11/1/2005&EndDate=11/30/2005&Net=1" _
        , ReadOnly:=True




This macro will be set to run every 14 days to update my tv schedule for my htpc. What I need to do is to program the first date in the url in my macro to change to today's date (the date the macro is run) and the 2nd date to change to 30 days later. For example if this macro is run on December 1 it would open the following url:

http://webapps.3abn.org/program/ViewWebSchedule.cfm?StartDate=12/01/2005&EndDate=12/30/2005&Net=1

If run on November 15 it would be:

http://webapps.3abn.org/program/ViewWebSchedule.cfm?StartDate=11/15/2005&EndDate=12/15/2005&Net=1

I would really appreciate any help in knowing how to do this. I would be happy to explain any other details.

---

Conditional Formatting In Excel Based On Specific Cell Updates
Hello,

I am creating an excel spreadsheet to track work progress and am having difficulty in setting up some conditional formatting on a column.

I have a header column as below:

Owner Coded Reviewer Review Number Status


Now underneath each of the above column headers, the data entered in each of the cells has an effect on what happens in the status column.
The status column has the following formaul entered in each cell:


Code:
=IF(J4<>"",IF(K4>=1,IF(M4<>"",IF(N4<>"","Ready for Rework (Post Review)","Review in Progress"),"Ready for Review"),"Schedule in Progress"),"Not Started")
Now for each of the 5 states which the formula can set in the status column I want to associate a colour - different to each other.

To do this i have created the following VBA:

Code:

Sub UpdateStatusColumn2()

Dim s As String

For Each c In Worksheets("Sheet3").Range("O2:O909").Cells

s = c.Value


Select Case s

Case s = "Not Started"
c.Activate
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

Case s = "Schedule in Progress"
c.Activate
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With

Case s = "Ready for Review"
c.Activate
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Case s = "Review in Progress"
c.Activate
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With

Case s = "Ready for Rework (Post Review)"
c.Activate
With Selection.Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

Case s = "Closed"
c.Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With

End Select

Next

End Sub
My spreadsheet has almost 1000 rows and hence I do not want this to check every row when called.

What I want is for when the Owner, Coded, Reviewer, Review Number, or Status cells are updated for a particular row, then the above procedure UpdateStatusColumn2 is ran only on the same row for the cell in the Status column.

How do I do this? I know the for loop a the start will require updating as it will now no be required, but how do I get VBA to run the procedure only a single row when a cell underneath Owner, Coded, Reviewer, or Review Number is modified?

I should also say that I tried to use the Conditional Formatting within the Format menu but it only lets me use 3 conditions - which is not enough

I would appreciate all help offered.

Thanks,

W.

---

Populate Listbox1 With Sheet Names Based On Cell Values
G'day,

What I'm trying to do is to populate a listbox with sheet names if, for example, cell B2 in a sheet has a value.

Here is my code:
Code:
Private Sub UserForm_Initialize()
Dim i As Integer
'Make sure the ListBox is clear
ListBox1.Clear
'For each sheet, add to listbox if cell B2 has an entry
For i = 1 To ActiveWorkbook.Sheets.Count
If Range("B2").Value > 0 Then
ListBox1.AddItem Sheets(i).Name
End If
Next
i = 0
End Sub
All this does is to populate the listbox with all the sheet names. I need to populate this listbox so the user knows what sheets have cell entries (i.e. each sheet is already set up, but if the user has entered any data, cell B2 will have a value - i.e. a text entry).

Thanks,

Mitch

---

Determining Text Cell Width In Flexgrid Based On Content
I would like to resize a particular cell's width and height depending on the content. I have searched around for an accurate way of determining the width/height of a particular string in a particular font but with out success.

Does anyone have any ideas?

Dan.

---

If/Then Statements In A Macro Based On Variables In A Sheet In A Different Workbook
I'm trying to get some if / then statements to run, checking against the values of cells in a different workbook than the macro / module is located. My problem is that the the statements only run one time when the macro runs. At this time there is nothing in the cells in question so the statements have nothing to compare.

Ideally I would like to put the statements into Private Sub ComboBox1_Change() of the sheet containing the variables to be examined as I would like the statements to run when the value of this combo box changes.

Again to clarify the macro that creates the combo box on this sheet is in another workbook that runs as an add-in.

I cannot hard code the statements here because the file is overwritten everytime I run the report that creates the file.

My seccond idea was to loop the if then statements in the macro so that they continue to run and would check the data in the other woorksheet. This is not a real solution for me bacause if the new workbook is saved after the macro creates the combo box and runs the formulas, the if then statements are still not there for future use.

THE QUESTION:
Is there a way to take a few lines of code (the if / then statements) from the module of one workbook and put them into a Private Sub in a SHEET of another worbook.
NOTE: There is NO MODULE in the seccond workbook nor would I like there to be if at all preventable. I just want the macro to move the statements into Private Sub ComboBox1_Change() in a sheet in the new workbook.

Here's my code. This is saved as an xla called RemedyTicketCount.xla
Sub RemedyTicketCount()
'
' Remedy Ticket Count Macro
' Macro recorded 3/31/2004 by Will Day
'

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 = "[RemedyTicketCount.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
If ActiveSheet.Range("e2").Value = "Matthew Gray" Then
ActiveSheet.Range("f2").Value = "GRAYMT"
End If
If ActiveSheet.Range("e2").Value = "James Pence" Then
ActiveSheet.Range("f2").Value = "PENCEJA"
End If
If ActiveSheet.Range("e2").Value = "Chuck Patenaude" Then
ActiveSheet.Range("f2").Value = "PATENACH"
End If
If ActiveSheet.Range("e2").Value = "Jimmy Zheng" Then
ActiveSheet.Range("f2").Value = "ZHENGJI"
End If
If ActiveSheet.Range("e2").Value = "Armando Ruiz" Then
ActiveSheet.Range("f2").Value = "RUIZAR"
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

End Sub

---

Call Macro ~change Macro
I want to create a print preview from a access rapport

I'm using this code:

Code:
Private Sub Command1_Click()
Dim A As Object

Set A = CreateObject("Access.Application")
A.Visible = true
A.OpenCurrentDatabase (App.Path & "
ewP2.mdb")
A.docmd.RunMacro "macroprinten"
ENd SUb
the first macrocommand is openreport + report name
at this instruction there is a posibility to specify a WHERE instruction.

Is it posible to chance this where instruction in visual basic
?


Many thx
grz
EBS

---

New Value To A Cell Through Macro.
Hey group. I need to import a new value to a cell through macro. How do I actually insert it in a cell once I have correct "A" ?

Sub Macro2()

Dim A As String
Dim B As String
'
Range("J2").Select
B = ActiveCell.Text
A = Replace(B, "/", "")

Range("H2").Copy Destination:=A

End Sub

---

Running A Macro When A Cell Value Changes
Hi
hope somone can help...

How can I get a macro to run when the value in cell b1 changes.
Cell B1 contains a data validation list, and when this changes i want the spreadsheet to update its self.

Any ideas would be appreciated.

Many Thanks

Robin

---

Cell Formula From A Macro
I'm trying to insert formulas into cells from a macro in the R1C1-style. I can get that to work, but now I would like to add in a variable to the formula, it appears to work fine in A1 notation. Something like this works...

Code:
Dim somer
Range("B28").Select
somer = ActiveCell.Address
Worksheets("Dataform").Range("A30").Formula = "=IF($A$28<" + (Range(somer).Address) + ",1,2)"
But when I modify it like this

Code:
Dim somer
Range("B28").Select
somer = ActiveCell.Address
Worksheets("Dataform").Range("A30").FormulaR1C1 = "=IF(R28C1<" + (Range(somer).Address) + ",1,2)"
I get an error. I'm thinking it's due to how the cell address is being set in the variable but can't figure out how to do it correctly or if that's even the problem.
Help?

---

Excel: Using A Cell Value To Run A Macro
I've got a spreadsheet that is always open and watching live data from another program (WonderWare). I need to figure out how to use a cell value (which in this case is discreet, always a "1" or a "0") to run a macro. I have the macro written and it works, but it needs to run when the cell value changes. It only needs to run one time, but each time the value changes it needs to run.


any thoughts?
thanks
..Brady

---

Change A Cell, Change The Worksheet
Is there a way to change a cell in Excel and have that make changes to a worksheet automatically??

For instance. If I type an "m" into cell A1, I want Rows 10-15 to be hidden, but if A1 is anything other than and "m" I want those row to be shown?

Any help would be greatly appreciated.

Thanks.

---

Change A Cell, Change The Worksheet
I posted this before, but in the wrong section, it was accidentally posted under Databases. Sorry if you're reading this twice.

Is there a way to change a cell in Excel and have that make changes to a worksheet automatically??

For instance. If I type an "m" into cell A1, I want Rows 10-15 to be hidden, but if A1 is anything other than and "m" I want those rows to be shown?

I got this code from a helpful poster, but was having trouble making it work:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1").Value = "m" Then
        Rows("10:15").Select
        Selection.EntireRow.Hidden = True
    Else
        Rows("10:15").Select
        Selection.EntireRow.Hidden = False
    End If
End Sub

When I pasted the code into the VBA editor, and tried to type an "m" into cell A1, nothing happened.
I thought maybe, I was missing some property or something. Can any one help?

Any help would be greatly appreciated.

Thanks.

---

Cell Range Naming In Macro
im getting my self into all sorts of problems in Visual basic and excel could anyone help with this
Code:
Dim Rg As Range

Sheets("Classes").Select
Range("A2:A500,H2:H500").Select
Set Rg = Selection.SpecialCells(xlCellTypeConstants, 3)
Set Rg = Application.Union(Rg, Selection.SpecialCells(xlCellTypeFormulas, 3))
Rg.Select
'
End Sub
the code above selects a range of cells. What would be the code to which it gives these selected cell a name. (Cell range naming)

---

Activate Cell In A Searching Macro
Hi,

I try to make working this searching function (Quoted sub) with two simple functions I don't know how to implement. When the unique row (there will be never more than one row found) is found, I want the active cell to be "Worksheets("UE").Range("A" & this row found)" and I want to copy in a variable the number of the row we are in. I tried several thing but it seems not working as far as now. Any ideas?

Thx!

Werner



Quote:





Sub Acces_dossier()

Dim rngUEData As Range
Dim rngUE As Range

Set rngUEData = Worksheets("UE").Range(Worksheets("UE").Range("CHAMP_DÉBUT_BD").Offset (1, 0), Worksheets("UE").Range("A65536").End(xlUp))

Dim IDUform As Long

'Variable corresponding to the IDU from the form.
IDUform = Worksheets("Formulaire").Range("Y2").Value

Application.Calculation = xlCalculationManual

For Each rngUE In rngUEData

If Trim(UCase(rngUE)) = Trim(UCase(IDUform)) Then
'It's here I want to implement those function logically.
Sheets("UE").Select
'rngUE returns me the value of the Y2 cell, not the UE row cell place
Range(rngUE).Activate
End If
Next rngUE
Application.Calculation = xlCalculationAutomatic

End Sub

---

Assign Macro When Cell Is Clicked
I have a range of cells (say A3:E3). If the user clicks on any of these I want to bring up an input box. I don't know if I should use Worksheet_Change since I don't want them to manually put anything in the cell, but have it activate just by selecting the cell. Is there any sort of worksheet_click event?

Also, if I use ByVal Target as Range (or something like that), how do I test for the range of A3:E3 rather than just one single cell?

Thanks!

---

Adding Cell Values In A Macro
I'm trying to add the values of two cells within a macro and then insert the sum into one of those cells.

Not sure what to use here.

I tired
Code:
Worksheets(strStation).Range(Cell7).Value = (SUM(Cell7:Cell8))
Where Cell7 and Cell8 are variables for the two cell locations I want to add together and then put the result in the Cell7 location. And that just gave me a compile error.

help anyone?

---

Goto A Particular Cell (excel Macro)
Hi,
I am working on an excel macro.
Is there a method whereby I can take the control to a particular cell in the current active sheet.
e.g if the control ( cursor) is in say cell C 10. when the user runs the macro the control (cursor) should go in cell S 22.
How can I do that.
amol

---

Looking Up A Cell And Changing The Contents In A Macro...
Hey all, hope you can help with this

OK, heres my problem:

Ive been making a Log in script in Excel using VB macros, and ive got as far as the user can register, log in, and it brings up his/her details like name, address, age etc. Now what im really stuck with is how to make it so the user can change their details. Basically what i need is the following, with my blanks filled in:

if Sheets(1).Range("cell with their name in") <> Sheets(1).Range("Cell with name stored in DB") Then

'the bit i dont know:

lookup their name in DB, and make it equal Sheets(1).Range"(cell with their name in")

End If

So the bit i dont know how to do is to make it so i lookup their name in the database (using something like a glorified vlookup, hope u can tell me what lol) and make that cell equal another cell of my choice...and this happens when one cell is not equal to another (when there name in DB is not equal to the name they change it to)

Any help will be greatly appretiated...ive been staring at it blankly for about an hour now *sigh*

Thanks,

Ross

---

From Macro Write Text To Cell
I'm using checkboxs on a form, calling it from a macro and then based on the choice a number gets written to a cell.

Looks like this.


Code:
If ChooseCCOutput1.cc1 = True Then
Worksheets("work").Cells(4, 2) = 8
Unload ChooseCCOutput1
ClosureAssign
End If

Here I'm putting the number 8 in the cell. In a different section I would like a text string to write to the cell instead based on a choice from a form. Just putting the text after = doesn't get it. Can someone tell me what I should do instead?

Thanks!

---

Excel - Run Macro When Cell Selected
Anybody know if and how I can start a macro whenever I select a cell, any cell? Thanks.

What I want to do is display the cell's value, similar to the way a comment would be displayed. The cells currently contain formulas.

Thanks.

---

Excel Macro-image To Cell
I need to create a new toolbar button that when pressed will insert a tiff image into a cell within Excel 2000. I know how to create the toolbar button and how to apply the macro but I need the VB code .

Thanks

---

Excel Macro For Cell LastModified
Hey all
I have a cell which needs to put a time stamp to keep track of the last modified.
Any ideas??

Thanks a lot

---