Change value of a cell by forming a button

[mshahbaz]

Hi,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Material 01[/TD]
[TD]Step 1[/TD]
[TD]NEXT Button[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]Material 02[/TD]
[TD]Step 3[/TD]
[TD]NEXT Button[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]Material 03[/TD]
[TD]Step 7[/TD]
[TD]NEST Button[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[/TR]
</tbody>[/TABLE]

In column 2, there is always the status of ongoing material in production.

My question is, if there is a NEXT button in column 3, and when i press it
the step 1 should automatically changes to step 2 and so on.

Shahbaz

 

[My Aswer Is This]

Why not use this:

Put the value "Step" in Column "B" header which is row(1)
Then when you double click on any number in column "B" the number will advance by one. This way you do not need a Button in every row and the coding needed in every button.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Column = 2 And Target.Row > 1 Then Target.Value = Target.Value + 1
End Sub

 

[mshahbaz]

Thanks

In column B actually it is not the no. step.

Instead it is the name of steps, e.g. under-process, imports, etc.

There are many steps.

How should i place the name of the step instead of numbers.


Regards

Why not use this:

Put the value "Step" in Column "B" header which is row(1)
Then when you double click on any number in column "B" the number will advance by one. This way you do not need a Button in every row and the coding needed in every button.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Column = 2 And Target.Row > 1 Then Target.Value = Target.Value + 1
End Sub

 

[My Aswer Is This]

So is this list of steps someplace on you sheet?

Like maybe in Range("P2:P14")

You could enter the steps in order 1 to 20 for example

Range("P1") "Briefing"
Range("P2") "Safety Training"
etc.

Then in Column ("Q") of every row The script enters a number like 1 then next time 2

Now the script when it sees a 1 in column ("Q") it would put "Briefing" in Column "B"

If it sees a 2 in column("Q") it will put "Safety Training" in column ("B")
The script starts working when you double click on row(2) and any row below.

If something like this would work try this:

We can modify things if needed later.

Here set things up like I said and try this:
Double click any cell in column "B" to activate this script.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 2 And Target.Row > 1 Then
Target.Offset(0, 15).Value = Target.Offset(0, 15).Value + 1
Target.Value = Cells(Target.Offset(0, 15).Value, 16).Value
End If
End Sub

 

[mshahbaz]

Thanks

 

[My Aswer Is This]

So are you saying this worked for you?

Thanks

 

[mshahbaz]

Yes, it worked and unfortunately, my tasks are changed last week.
Hence, i couldn't able to use it frequently.

Regards

 

[My Aswer Is This]

Not sure what you mean but the way I wrote the script it looks in a certain place on the sheet for the tasks. Alll you should have to do is relocate the task in the order you want. If you need more help explain your new situation.

Yes, it worked and unfortunately, my tasks are changed last week.
Hence, i couldn't able to use it frequently.

Regards

 

[mshahbaz]

I meant that i just started to use your provided VBA code, and it was working; and on the same day
my company did some changes in my responsibilities, hence i don't need that anymore.

I have a new question:

If i delete a cell content in column D; i want a "Warning" message before deleting anything.
I don't want to use VBA for this purpose, since the file is very important and i can't take any risk.

Is there any method without VBA?

Regards

Not sure what you mean but the way I wrote the script it looks in a certain place on the sheet for the tasks. Alll you should have to do is relocate the task in the order you want. If you need more help explain your new situation.

 

[My Aswer Is This]

There is no way I know of without Vba.