Need Help with the VBA Coding ... not even sure if it can be done by simple formula a

muin

New member
Joined
Jul 14, 2013
Messages
3
Reaction score
0
Points
0
I have three sheets named Sales, Production and Logistics. I have a column named Order Status. I have created three name ranges (stat1, stat2 & stat3).
Now my question is if a user from any of the above mentioned departments choose the current status of an order, the same should be reflected in the other two sheets.


Ex: If a guy from logistics select "pending delivery" both the sales team and Production team should see the same and vice a versa.


Can somebody please help me with this??


I sincerely appreciate all your help and Thanks a ton in advance.

** Find the sample sheet attached.
 

Attachments

  • Book1.xlsx
    10.2 KB · Views: 25

muin

New member
Joined
Jul 14, 2013
Messages
3
Reaction score
0
Points
0
I have three sheets named Sales, Production and Logistics. I have a column named Order Status. I have created three name ranges (stat1, stat2 & stat3).
Now my question is if a user from any of the above mentioned departments choose the current status of an order, the same should be reflected in the other two sheets.


Ex: If a guy from logistics select "pending delivery" both the sales team and Production team should see the same and vice a versa.


Can somebody please help me with this??


I sincerely appreciate all your help and Thanks a ton in advance.

** Find the sample sheet attached.

Can someone help me with this please... Its a bit urgent.

Thank You for the support.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi muin,

The following code should be placed in the ThisWorkbook module (see my signature if you need clarification on this.)

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    Dim sVal As String


    Select Case Sh.Name
        Case Is = "Sales", "Production", "logistics"
            If Target.Address = "$B$3" Then
                Application.EnableEvents = False
                
                sVal = Sh.Range("B3").Value
                Worksheets("Sales").Range("B3").Value = sVal
                Worksheets("Production").Range("B3").Value = sVal
                Worksheets("logistics").Range("B3").Value = sVal
                
                Application.EnableEvents = True
            End If
        Case Else
            'Do nothing
    End Select


End Sub

For reference, this will link B3 on each sheet. If you move your cells to a different location, the code will need to be updated.

HTH,
 

muin

New member
Joined
Jul 14, 2013
Messages
3
Reaction score
0
Points
0
Hi Ken,

First of all thanks very much for the prompt reply, i tried using your code and also used ur signature link to see how it needs to be done. I tried but it didnt work !! Perhaps i am doing something wrong?. Also i need this for the whole sheet not just one cell. If you can give me your email, i shall attach my work, so that you can go through it and come up with a solution. Its important and urgent as well.

Waiting for your reply.

Hi muin,

The following code should be placed in the ThisWorkbook module (see my signature if you need clarification on this.)

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    Dim sVal As String


    Select Case Sh.Name
        Case Is = "Sales", "Production", "logistics"
            If Target.Address = "$B$3" Then
                Application.EnableEvents = False
                
                sVal = Sh.Range("B3").Value
                Worksheets("Sales").Range("B3").Value = sVal
                Worksheets("Production").Range("B3").Value = sVal
                Worksheets("logistics").Range("B3").Value = sVal
                
                Application.EnableEvents = True
            End If
        Case Else
            'Do nothing
    End Select


End Sub

For reference, this will link B3 on each sheet. If you move your cells to a different location, the code will need to be updated.

HTH,
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
First off, my apologies, as that link isn't as clear for this kind of code. This code needs to go in the ThisWorkbook module, not a standard code module. So look for the ThisWorkbook object, put the code there, then remove the standard module you created.

Now... for the whole sheet... if I give you code to do that, we're essentially creating 3 exact copies of the same worksheet. In addition to being complete duplication, it will be HORRENDOUSLY slow to do, so why?

(There is an option to upload a workbook if you click "Go Advanced" for your reply.)
 

bigdee008

New member
Joined
Aug 5, 2013
Messages
1
Reaction score
0
Points
0
Not Sure if Im in the right thread. Im looking for a code to run on a column.

e.g
Cells in Column D = Low, Medium, or High
Cells in Column E = Low, Medium, or High

Column F should have the following conditions:


1. Green: LL(Low Probability, Low Impact), LM (Low Probability, Medium Impact), ML (Medium Probability, Low Impact)

2. Yellow: LH (Low Probability, High Impact), MM (Medium Probability, Medium Impact), HL (High Probability, Low Impact)

3. Red: MH (Medium Probability, High Impact), HM (High Probability, Medium Impact), HH (High Probability, High Impact).


Please help.

Thanks
 
Top