Making it work in a Dynamic method

[kalua1231]

Hello Chandoo community i am trying to get this thing to work as a dynamic means. Would appreciate an extra help. Thanks again for your time.

1st. When i want to change the number of five to two if there is already a two on column "F" have it move to three
three to four
four to five
and so on

2nd. when changing the description on column "F" if i click on ZCOMPLETED this will send it to row under description ZCOMPLETED. If i click on the generate button i want it to automatically work as a dynamic means. That if description 1 is done move "2 *" to "1 *"
move "3 *" to "2 *"
move "4 *" to "3 *"
and so on.

Sub openuserform()
NumberArange.Show
End Sub

Private Sub CommandButton1_Click()
NumberArange.MyNumber.Caption = NumberArange.MyNumber.Caption + 1

End Sub

Private Sub CommandButton2_Click()
NumberArange.MyNumber.Caption = NumberArange.MyNumber.Caption - 1
End Sub



Private Sub CommandButton3_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim rw As Range
Set ws = Worksheets("GENERAL")

For Each rw In ActiveSheet.UsedRange.Rows
If rw.Cells(1, 5) = Label3.Caption And rw.Cells(1, 4) = Label4.Caption Then
rw.Cells(1, 6) = MyNumber.Caption & Right(rw.Cells(1, 6), Len(rw.Cells(1, 6)) - InStr(rw.Cells(1, 6), " ") + 1)
End If
Next rw

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
End Sub

Private Sub CommandButton4_Click()
Unload Me
End Sub




Private Sub CommandButton5_Click()
Dim rw As Range
Dim mx As Integer
Dim lft As String
mx = 0
For Each rw In ActiveSheet.UsedRange.Rows
If InStr(rw.Cells(1, 6), " ") > 0 Then
lft = Left(rw.Cells(1, 6), InStr(rw.Cells(1, 6), " ") - 1)
If IsNumeric(lft) Then
If rw.Cells(1, 5) = ActiveCell.EntireRow.Cells(1, 5) Then
If Val(lft) > mx Then mx = Val(lft)
End If
End If
End If
Next rw
MyNumber.Caption = mx + 1
End Sub

Private Sub CommandButton6_Click()
    MyNumber.Caption = CommandButton6.Caption
End Sub

Private Sub CommandButton7_Click()
    MyNumber.Caption = CommandButton7.Caption
End Sub

Private Sub MyNumber_Click()

End Sub

Private Sub UserForm_Activate()
If ActiveCell.EntireRow.Cells(1, 6) <> "" Then MyNumber.Caption = Left(ActiveCell.EntireRow.Cells(1, 6), InStr(ActiveCell.EntireRow.Cells(1, 6), " ") - 1)
If ActiveCell.EntireRow.Cells(1, 5) <> "" Then Label3.Caption = ActiveCell.EntireRow.Cells(1, 5)
If ActiveCell.EntireRow.Cells(1, 4) <> "" Then Label4.Caption = ActiveCell.EntireRow.Cells(1, 4)

End Sub

 

[kalua1231]

Hi there guys, letting everyone know that this question is now cross posted in two different sites

https://www.experts-exchange.com/questions/28967480/Numbers-dynamic-manner.html

and recently

http://answers.microsoft.com/en-us/...-8671-473d-b5d2-ffb5faa215af?tm=1472882941926

being considerate of everyone time, thanks again for your time and your help.

 

[kalua1231]

it seems like there is no luck in trying to solve this question?

 

[YasserKhalil]

There is no response as you have to explain more
Try this code for worksheet before double click (I am not sure of what you need exactly)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim dccolumn As Integer
Dim dcvalue As String

dccolumn = ActiveCell.Column
dcvalue = ActiveCell.Value


If Application.Intersect(ActiveCell, [headers]) Is Nothing Then

      Range("C2").Value = Target.Value
        Cancel = True
       
      If ActiveCell.Value <> "" Then
        On Error Resume Next
        ActiveSheet.ShowAllData
       
        ActiveSheet.ListObjects("Table134").Range.AutoFilter Field:=6, Criteria1:=Selection.Text, Operator:=xlOr, Criteria2:="DIVIDER"
       

       
        End If
   
    End If
   
End Sub

 

[kalua1231]

Thanks for the response Yasserkhalil, i will give out more simpler demonstration on the images located on this comment,

if one of the task or subtask number is change to another number that is already in used. The task that has the number already in used will automatically change number and moved down.



please let me know if this helps.