Posts by Chino21

Re: Timed textbox refresh

Quote from cytop;752544

I cannot answer, I avoid shared workbooks like the plague so cannot comment on what code may. or may not, do in that situation.

ah ok no worries, thanks for the reply. i'll try to figure it out. Or if somebody might also help me out i'll appreciate it.

Re: Timed textbox refresh

Quote from cytop;752349

What's to refresh? A textbox just sits there until you do something unless it is linked to a cell/other data that's updated somehow. You need to give a little more detail.

Thank you for the reply cytop,

There will be two foms frmmain and frmmemo. In frmmemo im planning to add a button(cmdsave) and a textbox(txtmemo). So what will happen is when you type a word in txtmessage let's say "Hello" this message will be saved in cell A6. Now with frmmain there will be a textbox(txtalert), this textbox will show the message saved in cell A6. The worksheet will be shared, so technically what im trying to figure out is since I can modify what is the message in A6 thru frmmemo, is there a way like putting a timer in txtalert so that it will refresh it self and read A6 lets say every 2 mins so that making sure that what ever memo in A6 will show up.

Hi guys,

Is this possible? What i'm tying to figure out is there a way to automatically refresh a textbox(textbox1) every 2 mins. then show the value in A6. It's somewhat like a timer.

Looking forward for your kind help.

Re: Adding x days to date

Quote from Luke M;748329

Using the default names for the items in your user form, code would be like:

Code

Private Sub ComboBox1_Change()
Dim daysAdd As Long
Select Case Me.ComboBox1.ListIndex


Case 0
    daysAdd = 0
Case 1
    daysAdd = 30
Case 2
    daysAdd = 90
Case 3
    daysAdd = 365
End Select
Me.TextBox2.Value = CDate(Me.TextBox1.Value) + daysAdd
End Sub

Display More

Luke thank you for the reply.. I have already found my way and to tell you honestly we almost have the same code. Again Thank you sooooooooooooooooooooooooooooooooooooo much. Your a STAR!!!

Please consider this thread SOLVED.

Hi guys,
Hope you can help me out. I have a form with a combobox and two textboxes. Combobox shows 4 selections (First, Second, Third, Final), Textbox1 shows a date. What im trying to figure out is if I select First, Textbox2 will show date from Textbox1. If I select Second Textbox2 will show date from Textbox1 + 30 days. Textbox2 if Third will be Textbox1+90 days then Textbox2 if Final will be Textbox1+ 365 days (1 whole year).

Hope this is not too much to ask.

Hi guys,

Im having trouble figuring this out. I have 2 comboboxes and 7 textboxes. What it does combobox1(cboagent) will show all the names (Range B)which is already filtered in sheet "Report". Once a name is selected combobox2(cbodatadate) will show all the dates (Range C) that corresponds to the name selected. What im having trouble is how will I transfer the data from the cell to my textboxes.

Let me give an example just to make it clear:

Combo1 Combo2
Mike - 04/12/2013
- 05/15/2013
- 02/15/2014 <-- lets say I select this
- 03/15/2015

textbox1 = cell C, textbox2 = cell D, textbox3 = cell E, textbox4 = cell F,
textbox5 = cell G, textbox6 = cell H, textbox7 = cell I

Now if in case I change the date textboxes value will also change.

Really having trouble with this. Hope somebody can help me out

I've used this formula to filter the name and dates (myData):

=OFFSET('Report'!$B$2,0,0,COUNTA('Report'!$B:$B),1)

frmcoaching:

Option Explicit
Dim FArray As Variant
Dim DataList As Range, cel As Range, Rng As Range
Dim MyList As String
Dim ws As Worksheet


Private Sub UserForm_Initialize()


cboagent.Enabled = True


txtdate.Text = Date


Me.txtsupname.Text = frmlogin.cbousername.Text


ThisWorkbook.Sheets("Report").Activate


    Dim Found As Long, i As Long
    Sheets("Report Coaching").AutoFilterMode = False
    MyList = "myData"




    Set DataList = Range(MyList).Columns(1)
    DataList.Select


    Set DataList = Selection
    ReDim FArray(DataList.Cells.Count)
    i = -1
    For Each cel In DataList
        On Error Resume Next
        Found = Application.WorksheetFunction.Match(cel, FArray, 0)
        If Found > 0 Then GoTo Exists
        i = i + 1
        FArray(i) = cel
Exists:
        Found = 0
    Next
    ReDim Preserve FArray(i)
    Call BubbleSort(FArray)
    cboagent.ListRows = i + 1
    cboagent.List() = FArray


End Sub


Private Sub cboagent_Change()
Flag = True
    cbodatadate.Clear
    


    Set ws = Sheets("Report")
    With ws
        If Not ActiveSheet.AutoFilterMode Then
            ActiveSheet.Range("B2").AutoFilter
        End If
.Range(("B2"), .Range("B2").End(xlDown)).AutoFilter Field:=2, Criteria1:=Me.cboagent.Value
        Set Rng = .Range("myData").Columns(2).SpecialCells(xlCellTypeVisible)


             Set Rng = .Range("myData").Columns(2).SpecialCells(xlCellTypeVisible)
        For Each cel In Rng
            With Me.cbodatadate
                .AddItem cel.Offset(0, 0).Value
            End With
        Next cel
    End With
    Flag = False
   
End Sub


Private Sub cbodatadate_Click()


End Sub


Private Sub cbodatadate_Change()


End Sub


Private Sub cmdlogout_Click()
    
End Sub


Private Sub cmdrefresh_Click()
Unload Me
frmcoaching.Show
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        MsgBox "Sorry you must use the CLOSE Button"
        Cancel = True
    End If
End Sub


Sub BubbleSort(MyArray As Variant)


    Dim First As Integer
    Dim Last As Integer
    Dim i As Integer
    Dim j As Integer
    Dim Temp As String


    First = LBound(MyArray)
    Last = UBound(MyArray)
    For i = First To Last - 1
        For j = i + 1 To Last
            If MyArray(i) > MyArray(j) Then
                Temp = MyArray(j)
                MyArray(j) = MyArray(i)
                MyArray(i) = Temp
            End If
        Next j
    Next i
End Sub

Re: combobox 2 depending on combobox 1 value (Filtered Range)

Quote from jindon;746873

If you upload a workbook with comboboxes, it will be clearer that you have what kind of comboboxes on userform/wroksheet etc.

Thank you jindon for the reply.... I have already found my way...

More power guys

I hope somebody help me,

Sheet Name: Record
Comboboxes: Combo1, Combo2

This is where I need help, in Combo1 this should show filtered names from range B, if I selected a name in Combo1, Combo will list all dates (Range C) for the said name.

For example:

Kenny White 01/01/2013
Jenny Brown 01/03/2013
Mike House 02/10/2013
Kenny White 02/11/2013
Kenny White 03/21/2014
Michelle Miles 04/12/2014
Kenny White 04/17/2014
Kenny White 05/01/2015
Michelle Miles 05/12/2014
Kenny White 05/20/2015

Combo1: Kenny White

Combo2: 01/01/2013
02/11/2013
03/21/2014
04/17/2014
05/01/2015
05/20/2015

Hope somebody can help me out.

More power

Hi guys,
Hope you can help me out, I have a login password here that I need to put a privilege. Workbook (login) holds the data. Column A holds the user name, Column B will be the password and Column C should hold the provilege (Admin, Manager, HR, SUP, QA).

Admin privilege frmmain should show without limit. (frmmain.show)

Manager: frmmain.admin.Enabled = False

HR: frmmain.cmdadmin.Enabled = False
frmmain.cmdcoaching.Enabled = False
frmmain.cmdescal.Enabled = False
frmmain.cmdreport.Enabled = False
frmmain.cmdgraph.Enabled = False

SUP: frmmain.cmdadmin.Enabled = False
frmmain.cmdreport.Enabled = False
frmmain.cmdgraph.Enabled = False

QA: frmmain.cmdadmin.Enabled = False
frmmain.cmdcoaching.Enabled = False
frmmain.cmdescal.Enabled = False
frmmain.cmdtwin.Enabled = False

Currentlly here is my code:

Private Sub cmdlogin_Click()
Dim USERNAME As String
Dim PASSWORD As String
ilastrow = lastRowpub(1, Worksheets("login"))
USERNAME = UCase(frmlogin.cbousername.Value)
PASSWORD = UCase(frmlogin.txtpassword2.Value)
        
For Each cell In Worksheets("login").Range("a2:a" & ilastrow)


'If the username is correct
    If USERNAME = UCase(cell) Then
'Check password
        If PASSWORD = UCase(cell.Offset(0, 1).Value) Then
    With frmlogin
        .txtpassword2 = ""
        .cbousername.RowSource = "login!a2:a" & ilastrow
        .Hide
        frmmain.Show
        
End With


 Exit Sub
        
    Else
       MsgBox "Invalid Password. Please enter a valid Password"
       txtpassword2.Value = vbNullString
       Exit Sub
    End If
End If
'added code
Next
MsgBox "Sorry you are not registered. Please coordinate with the Admin", vbInformation
        
End Sub

I hope somebody can help me out.

Re: Listview error handler if onclick is null or empty

Quote from Halvar;744819

Chino, can you be more specific as to is "an error message that looks like nothing happened"?

If you just want for literally nothing to happen, add the following to your code (NOTE: it will mask all the errors in your code, meaning any error of any kind - not just when the user clicks where she's not supposed to):

Code

Private Sub ListView1_Click()
     
     
On Error GoTo errHandler
    With ListView1.SelectedItem
         
         
        txtdatecoached.Value = .SubItems(2)
        txtfocusedmetrics.Value = .SubItems(3)
        txtconnectedmetrics.Value = .SubItems(4)
        TextBox2.Value = .SubItems(7)
        TextBox3.Value = .SubItems(8)
        TextBox4.Value = .SubItems(9)
        txtmostimpactingbehavior.Value = .SubItems(5)
        txtsymptoms.Value = .SubItems(6)
        txtactionplan.Value = .SubItems(10)
         
    End With
     
errHandler:
Exit Sub
     
End Sub

Display More

Thank you for the reply Halvar, actually i've already tried that code ti is still giving me the same error (Run-time error '91' Object variable or With block variable not set)

Hi guys,

Again i might need your help... Is there anyway that I can put an error handler for my listview that if the area user click is empty it will show an error message or something that looks like nothing happened. The reason is that im receiving an error message everytime a user accidentally or purposely click the area where there are no data showing. Here is my code:

Private Sub ListView1_Click()


    With ListView1.SelectedItem
    


      txtdatecoached.Value = .SubItems(2)
      txtfocusedmetrics.Value = .SubItems(3)
      txtconnectedmetrics.Value = .SubItems(4)
      TextBox2.Value = .SubItems(7)
      TextBox3.Value = .SubItems(8)
      TextBox4.Value = .SubItems(9)
      txtmostimpactingbehavior.Value = .SubItems(5)
      txtsymptoms.Value = .SubItems(6)
      txtactionplan.Value = .SubItems(10)
      
    End With
    
End Sub

The program is working bu this is the only thing that im having trouble with. Hope you can help me out.

More power...

Re: 3 comboboxes problem

Quote from Chino21;744797

Hi guys,
Need some help here. i am having some trouble with the idea on how to create 3 comboboxes. I dont have any trouble creating two comboboxes but im stuck with the third one. This is how it goes.... combo2 value will depend on combo1 value and combo3 value will depend on combo2 value. Or is there anyway that the values for the comboboxes can be pulled from range.

Hope somebody can jump start my brain.

More power.

Already find my way....

Hi guys,
Need some help here. i am having some trouble with the idea on how to create 3 comboboxes. I dont have any trouble creating two comboboxes but im stuck with the third one. This is how it goes.... combo2 value will depend on combo1 value and combo3 value will depend on combo2 value. Or is there anyway that the values for the comboboxes can be pulled from range.

Hope somebody can jump start my brain.

More power.

Re: Filtering Listview with combobox value

Quote from cytop;742767

Which is it? ListView or ListBox ...?

The code is for a ListView, your commentary says ListBox. They're different thingd.

Regardless, neither has a 'Filter' function. You'll have to clear and reload with the relevant records each time the filter expression changes.

Oh Im sorry my bad... I was googling when I created the post.. its for a Listview (Listview1)..
What im looking for is for example in combobox I selected Paul, listview will only show Paul, its like filtering the Listview depending on the combobox selection.

Hi guys,
I hope somebody can help me out. I have a Listbox(Listbox1) and a combobox (cmbname), the names are in sheet (Login). Can you please help me out to filter the Listbox that if I choose a name, Listbox will only show the name and details for the said selection. BTW, Listbox is getting its information from sheet(Report).

Thank you in advance guys for the help...

Below is my code for the list view

Sub LoadListView()


  Dim C As Long
  Dim i As Long
  Dim FilterRow As Range
  Dim FilterRng As Range
  Dim N As Long
  Dim rng As Range
  Dim Total As Currency
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("Report")
    Set rng = Wks.Range("A1").CurrentRegion
    
    Set FilterRng = rng.SpecialCells(xlCellTypeVisible)
    Set FilterRng = Intersect(FilterRng, rng.Offset(1, 0))


    
   'Configure the appearance of the ListView object
    ListView1.ListItems.Clear
      ListView1.ColumnHeaders.Clear
        ListView1.View = lvwReport
           ListView1.Gridlines = True
              ListView1.FullRowSelect = True
                     'Add the Column Headers
                    For i = 1 To rng.Columns.Count
                           ListView1.ColumnHeaders.Add i, , rng.Item(1, i)
               Next i
'ListView1.ColumnHeaders(1).Alignment = lvwColumnRight
ListView1.ColumnHeaders(2).Alignment = lvwColumnRight
ListView1.ColumnHeaders(3).Alignment = lvwColumnRight
ListView1.ColumnHeaders(4).Alignment = lvwColumnRight
ListView1.ColumnHeaders(5).Alignment = lvwColumnRight
ListView1.ColumnHeaders(6).Alignment = lvwColumnRight
ListView1.ColumnHeaders(7).Alignment = lvwColumnRight
ListView1.ColumnHeaders(8).Alignment = lvwColumnRight
    
     If FilterRng Is Nothing Then Exit Sub
    
            'Fill in the Rows and Columns with data from the table
             With ListView1
             For Each FilterRow In FilterRng.Rows
            'Add a row - This also used to references the first column
             With ListView1.ListItems.Add
             'SubItem (other columns in the row) counter
     i = 1
    'Fill column 1
    .Text = FilterRow.Cells(1, 1)
                           'Fill the remaining columns
                            For C = 2 To FilterRow.Cells.Count
                            .SubItems(i) = FilterRow.Cells(1, C).Text
                            i = i + 1
                            Next C


 
   'Format the Grand Total Line in Bold
  With ListView1.ListItems(ListView1.ListItems.Count).ListSubItems
  .Item(.Count).Bold = True
 '  .Item(.Count - 11).Bold = True
   End With
ListView1.ColumnHeaders(1).Width = 50


ListView1.ColumnHeaders(2).Width = 80
ListView1.ColumnHeaders(3).Width = 300
ListView1.ColumnHeaders(4).Width = 65
ListView1.ColumnHeaders(5).Width = 50
ListView1.ColumnHeaders(6).Width = 100
ListView1.ColumnHeaders(7).Width = 90
ListView1.ColumnHeaders(8).Width = 100
   
End With
Next
End With
End Sub

Hi guys,

I m having some trouble what am I missing or managing my codes. Here is some of my troubles:

1. Form should check is checkbox Invalid Escalation is TRUE. If Invalid Escalation is TRUE form will be asking for an ID number and password; if Invalid Escalation is FALSE information will still be saved, ID number and password will be disabled and will be bypassed. Information will be saved to worksheet Report.
2. Worksheet report and UserPassword will always be hidden
3. Excel needs to auto save.
-------------------------------------------
PS:

Im not sure if this is possible, is there anyway that the infomation can be printed (hard copy) with a certain template?

Ex:

Date: 03/02/2015

This is to certify that (Name) was coached (Date). Coaching session for account (Account Number) for (Call Type).
(Name) was able to (Resolution). Call was (Root cause) with (Root cause2) with (Behavior). (Feedback and Coaching points)

Coached by: SUP/SME at (STATION)
--------------------------------------------------

Attached is a copy. Please check.

Your kindness and help is needed.
Chino

Hi guys,

Can somebody help me, Im trying to figure out how to print information that I saved with a certain temple.

ex: (what you see in the spreadsheet)
......A....B....C......D
1 Name ID Code Date
2 Chino 12 123 03/04/2015

Now I have txtname, txtid, txtcode, txtdate

What I want to figure out is how to print these information with a certain template. like:

This is to certify that Chino with an ID number 12
is requesting for the code 123 dated 03/04/2015.
Blah blah blah blah blah blah blah blah

Chino
Signature Needed

Is this even possible? Is there a code for this?

Hope somebody can help me.

More power,
Chino

Re: Showing data when button Generate is clicked

Noted Sir sorry for that..

Have a great day,
Chino

Re: Showing data when button Generate is clicked

Quote from trunten;615510

Just needed to make both your forms not modal and also set the text of the memo window which you weren't currently doing.
Also added the enter key behvaiour (it's a property of the text box which you set)

hth

trunten,
Thanks for the quick reply ya helped me alot and it all makes sense.... BTW just a follow up if it is not too much to ask... i have radio buttons (OptYes and OptNo) how can i include this that the clicked button will be included in the frmmemo. It should show up like ex: Order Valid: Yes. Here is my code BTW...

Public Sub setText(ByVal Name, address, phonenumber, datereceived, ordernumber, submittedby, comment As String)
    Dim header As String
        
    header = "***Order Memo Generator***" & vbLf & _
        "          ****************" & vbLf & _
        "Name: " & Name & vbLf & _
        "Address: " & address & vbLf & _
        "Phone Number: " & phonenumber & vbLf & _
        "Date Received: " & datereceived & vbLf & _
        "Order Number: " & ordernumber & vbLf & _
        "Submitted By: " & submittedby & vbLf & vbLf & _
        "Comment: " & comment & vbLf & vbLf & _
        "Rep Name & ID: Arvin A (1450320)"
    TextBox1.Text = header & vbLf
End Sub

Private Sub cmdgenerate_Click()
    frmmemo.Show
    frmmemo.setText txtname, txtaddress, txtphonenumber, _
            txtdatereceived, txtordernumber, txtsubmittedby, txtcomment
End Sub

Looking forward for the reply and help.

God Bless

Re: sum across tabs

Hi nicktr,

Hope you can attach the file that you are working at so that we can work on it.

We will be waiting for your reply.

Have a great day,
Chino