Solved

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

Posted on 2016-09-01
18
46 Views
Last Modified: 2016-09-02
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
Comment
Question by:Omar Hernandez
  • 9
  • 9
18 Comments
 

Author Comment

by:Omar Hernandez
ID: 41781815
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
 

Author Comment

by:Omar Hernandez
ID: 41781838
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41781845
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41781846
Can you explain more clearly what you want to do & I'll take a look
0
 

Author Comment

by:Omar Hernandez
ID: 41781860
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41781928
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
 

Author Comment

by:Omar Hernandez
ID: 41782018
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41782040
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
 

Author Comment

by:Omar Hernandez
ID: 41782046
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41782060
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
 
LVL 17

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41782090
Is this what you mean?
completed-9-1--2-.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41782095
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41782103
See my last upload. I'll check back later,.
0
 

Author Comment

by:Omar Hernandez
ID: 41782110
Eureka, That is exactly what i was looking for pardon me for not including userform on the post.
0
 

Author Closing Comment

by:Omar Hernandez
ID: 41782116
Thanks for your help Roy_Cox for your assistance really help me solve the issue i was having with the userform.
0
 

Author Comment

by:Omar Hernandez
ID: 41782135
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41782555
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41782557
Thanks for the feedback  and don't forget to use the Space Function
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now