• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

Show click information in the userform instantly and also adding ten space in column

ADD SUBS
***have it show the actual folder and task when is being open instantly.
***New Code to also copy and paste column "F" to the new row on column "F".
***Information being sent from the ADD SUBS userform to the new row. Have it skip ten spaces only in the "C" column before adding the given retrieve information from the userform.
 For example if the userform is sending "google" to column C have it send to column C as
"          google"

Add-Sub-task.pngTen-Spaces.pngTen-Spaces-and-copy-information-to-d.png
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("GENERAL")

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)

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for a Name number
If Trim(Me.textbox_name.Value) = "" Then
Me.textbox_name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If

'copy the data to the database
        ws.Cells(iRow, 3).Value = Me.textbox_name.Value
        ws.Cells(iRow, 4).Value = Me.Label4.Caption
        ws.Cells(iRow, 5).Value = Me.Label3.Caption
        ws.Cells(iRow, 6).Value = " *"
        ws.Cells(iRow, 9).Value = "2"
        

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.textbox_name.Value = ""
Me.textbox_name.SetFocus
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Open in new window

completed-9-1.xlsm
0
Omar Hernandez
Asked:
Omar Hernandez
  • 9
  • 9
1 Solution
 
Omar HernandezAuthor Commented:
Great" i have found what i need to do to have ten spaces before userform information is sent.

ws.Cells(iRow, 3).Value = Me.textbox_name.Value

change it to

ws.Cells(iRow, 3).Value = "          " & Me.textbox_name.Value
0
 
Omar HernandezAuthor Commented:
Hello there Experts Exchange community again i am here to share other solution for this i have also found how to copy and paste the information in row 6 to the new row in the six column.

Copy and paste

If ActiveCell.EntireRow.Cells(1, 4) <> "" Then Label4.Caption = ActiveCell.EntireRow.Cells(1, 4)

change the number and created a new label

If ActiveCell.EntireRow.Cells(1, 6) <> "" Then Label4.Caption = ActiveCell.EntireRow.Cells(1, 6)

and

ws.Cells(iRow, 6).Value = Me.Label5.Caption

now i just want to find how to make it the information show instantly when clicked on the userform.
0
 
Roy CoxGroup Finance ManagerCommented:
The correct way to do this in VBA would be to use the Space Function, like

MsgBox Space(5) & "Hello" & Space(5) &" World"

Open in new window


So you would use

ws.Cells(iRow, 3).Value = Space(10) & Me.textbox_name.Value

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Roy CoxGroup Finance ManagerCommented:
Can you explain more clearly what you want to do & I'll take a look
0
 
Omar HernandezAuthor Commented:
Sure Roy_Cox and thanks for your response

When i click on a cell Column ( D and E) will have it shown on the userform i have other userform not this one that show instantly when i click on it. i want it to display the information of column (D and E) Instantly when click.

Have a look on the first picture it is blank.
0
 
Roy CoxGroup Finance ManagerCommented:
This code will load your form with the required information in the labels. There is no need to switch off screen updating.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Target.Count > 1 Then Exit Sub
    Dim rownumber As Integer

    ''/// Target refers to the ActiveCell
    rownumber = Target.Row
    If Application.Intersect(Target, [headers]) Is Nothing Then
        If Target.Value <> "" Then
            Range("a1:i5000").Interior.ColorIndex = xlNone
            Range("a" & rownumber & ":F" & rownumber).Interior.Color = RGB(255, 255, 9)
        End If
    End If
    For r = 1 To 4
        Select Case r
        Case 1
            bgW = "ACTIVE"
            bg = RGB(255, 0, 0)     '   FF  00  00
        Case 2
            bgW = "ON DECK"
            bg = RGB(255, 102, 0)   '   FF  66  00
        Case 3
            bgW = "ON HOLD"
            bg = RGB(153, 102, 0)   '   99  66  00
        Case 4
            bgW = "COMPLETED"
            bg = RGB(0, 153, 51)     '   00  99  33
        End Select
        bgR = 0
        On Error Resume Next
        bgR = Application.Match(bgW, ActiveSheet.Range("C:C"), 0)
        If bgR > 0 Then Range("A" & bgR & ":J" & bgR).Interior.Color = bg
    Next r
    '           check cells for desired format to trigger the calendarfrm.show routine
    '           otherwise exit the sub

    Dim DateFormats, DF
    DateFormats = Array("m/d/yy;@", "m/d/yy")
    For Each DF In DateFormats
        If DF = Target.NumberFormat Then
            If CalendarFrm.HelpLabel.Caption <> "" Then
                CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
            Else
                CalendarFrm.Height = 191
                CalendarFrm.Show
            End If
        End If
    Next

    Select Case Target.Column
    Case 4, 5
        With Cmdbutton_Addsubs
            .Label3.Caption = Cells(rownumber, 5).Value
            Label4.Caption = Cells(rownumber, 5).Value
            .Show
        End With
    End Select

    '    Application.ScreenUpdating = True
End Sub

Open in new window


I don't understand what your code in the add task button is trying to do.
0
 
Omar HernandezAuthor Commented:
Thanks for trying, but did not work. when you download the file please click on this box shown on the picture. As you can see once you click on it you will not see any information on the userform.

Without you adding any information on the textbox click on the (ADD SUBTASK) butoon, you will then see an information display on the userform. What i want it to do is show the information instantly once i click on the ADD SUBTASK shown on the picture.
Hopefully this will help, thanks for your time.

click-here.png
0
 
Roy CoxGroup Finance ManagerCommented:
I have no idea what you mean. The code that I added automatically shows the form with the data in when you select a button in Columns D or E. You said that you wanted "automatic".
0
 
Omar HernandezAuthor Commented:
Description says

ADD SUBS
***have it show the actual folder and task when is being open instantly.

pardon me i left out the userform.

but, then again the actual picture shows what i mean
0
 
Roy CoxGroup Finance ManagerCommented:
Do you want load the form using the button on the sheet, but with the relevant data displayed without using the button on the form?
0
 
Roy CoxGroup Finance ManagerCommented:
Is this what you mean?
completed-9-1--2-.xlsm
0
 
Omar HernandezAuthor Commented:
Mr roy look at the first and second picture from this post.

when you look at the picture next to the word in big font "COLLEGE"
you will then see a small green box on it's right, the name on the green box is "ADD SUBS"
when you click on it what ever cell you click, column D and E from that particular row information would show on the userform but not instantly.
If you click on the userform button name "ADD SUBTASK" you will get an error and then see the actual information shown on the userform.


What i want it to do
Once you click on the box called "ADD SUBS" , if there is information on the D and E column have it show on the userform instantly.

Do you understand me now?
0
 
Roy CoxGroup Finance ManagerCommented:
See my last upload. I'll check back later,.
0
 
Omar HernandezAuthor Commented:
Eureka, That is exactly what i was looking for pardon me for not including userform on the post.
0
 
Omar HernandezAuthor Commented:
Thanks for your help Roy_Cox for your assistance really help me solve the issue i was having with the userform.
0
 
Omar HernandezAuthor Commented:
Hey there Roy wanted to know what was missing or what did you do in addition to have the information shown on the userform.
0
 
Roy CoxGroup Finance ManagerCommented:
Look at the code behind the button

Sub cmdbutto_addsubs()
''/// do not alow multiple cells to be selected
If Selection.Count <> 1 Then Exit Sub
Dim lRw As Long
lRw = ActiveCell.Row
     With Cmdbutton_Addsubs
            .Label3.Caption = Cells(lRw, 4).Value
            .Label4.Caption = Cells(lRw, 5).Value
            .Show
        End With
End Sub

Open in new window


Now that I know that's what you wanted I would add another check so that it only works on columns D or E.

The code first gets the row number of the selected cell then simply adds the required information from the cells to the Labels. You should delete the button on the UserForm and it's code.

Here's replacement code

Sub cmdbutto_addsubs()
''/// do not alow multiple cells to be selected. Only use Columns D or E
    With Selection
        If .Count <> 1 Or .Column > 5 Or .Column < 4 Then
            MsgBox "Please select one cell in Column D or E", vbCritical, "Cancelled"
            Exit Sub
        End If
    End With
    Dim lRw As Long
    ''/// get the row selected.
    lRw = ActiveCell.Row
    With Cmdbutton_Addsubs
        ''/// add the information from Columns D & E on that row
        .Label3.Caption = Cells(lRw, 4).Value
        .Label4.Caption = Cells(lRw, 5).Value
        ''/// display the userform
        .Show
    End With
End Sub

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
Thanks for the feedback  and don't forget to use the Space Function
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now