Posts by rmoreno

Re: Show Macro Completion Progress Checklist using Userform

How else can this be done?

Re: Show Macro Completion Progress Checklist using Userform

I just stepped through the code. When I step through the code the form comes up, the checkboxes are seen and they turn TRUE after each macro. It works, but when I try to execute the code from the macro call button none of that is seen on the form. The form is blank. Does this help anyone in trying to resolve this problem?

Re: Show Macro Completion Progress Checklist using Userform

Thanks SomeDude, I tried your suggestion but it did not work. I also tried DoEvents but it also did not work.

Hello everyone, I have been working on a userform that shows the users when a series of macros have completed using checkboxes on a userform. I would like the checkboxes to show TRUE after each macro has completed running until the last macro is called and run. The problem is, the userform shows up but none of the checkboxes are displayed (complete blank) until all the macros have run and then they all show up and are TRUE. Here is the code I have been working on. Hope someone can help me figure out what I am doing wrong. Thanks

Sub ShowMacroProgress()
    frm_DataUpdate.Show vbModeless
End Sub
 
Sub UpdateData_Step1()
    Call ShowMacroProgress
    frm_DataUpdate.chk_UpdateHeader.Value = True
    Call HeaderSummary_DataCapture
    Call UpdateData_Step2
End Sub
 
Sub UpdateData_Step2()
    frm_DataUpdate.chk_UpdateGrade.Value = True
    Call GradeDetail_DataCapture
    Call UpdateData_Step3
End Sub
 
Sub UpdateData_Step3()
    frm_DataUpdate.chk_UpdatePassFail.Value = True
    Call PassFailAnalysis_DataCapture
    Call UpdateData_Step4
End Sub
 
Sub UpdateData_Step4()
    frm_DataUpdate.chk_UpdatePassFail.Value = True
    frm_DataUpdate.Hide
    MsgBox "Data Has Been Updated"
End Sub

I have written a VBA module to Sort Filtered Unique data in a specific location. The problem is, I do the same code four times.
Is there a more efficient way to use the same sorting method without having to do the same code four times?
I have supplied a copy of my code for all to review:

'Sort Unique Values: TestGrp
Range("SgDV_TestGrp").Sort Key1:=Range("SgSort_TestGrp"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


'Sort Unique Values: Product
Range("SgDV_Product").Sort Key1:=Range("SgSort_Product"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


'Sort Unique Values: Machine
Range("SgDV_Machine").Sort Key1:=Range("SgSort_Machine"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


'Sort Unique Values: SampleType
Range("SgDV_SampleType").Sort Key1:=Range("SgSort_SampleType"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Re: Advanced Filter Unique Data Copying Not Working

Well I figured out what I was doing wrong. I no longer need any help with this now. Thanks to anyone who viewed this Thread.

Hello All,
I am trying to copy unique column data to a different worksheet but when I run the code I only get one item. I think it may something to do with the fact that the data I have in my column has spaces and commas. Is there any way for the Advanced Filter xlFilterCopy to work in a column where the data has spaces and commas? (Example: WP, Production, 300C, Alum, 1.25 mil)

Sub UniqueList()
  
    Dim r As Range, r1 As Range
    Set r = Sheets("SourceData").Range("A8:A1000")
    
    r.AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Sheets("UniqueValues").Range("A4"), _
        UNIQUE:=True
    Set r1 = Range("A4", Range("A4").End(xlDown))
    r1.Sort Key1:=r1, Order1:=xlAscending, Header:=xlNo
    
End Sub

Re: OnTime Interval Applyed Every 15 Seconds Using Different Autofilter Criteria

Disregard this posting. I am going in a different direction.

Thanks.

Re: Checkbox on Chart Sheet Keeps Moving Around

Disregard this posting. I changed to a text box and no longer use a check box.

Thanks.

Re: Using OnTime Schedule to Set or Reset A Cell Value

Disregard this posting. I have resolved this issue.

Thanks

Hello All,
I just can't seem to get the syntax correct for setting or resetting a cell value using the OnTime schedule function.
It looks like the cell value changes randomly and after several cycles it just runs non-stop very, very quickly and I have to use the ESC key to break out of the loop.
Can someone show me what I am doing wrong please?

Here is my code:

Sub RunOnTime()
    dTime = Now + TimeValue("00:00:10")
    Application.OnTime dTime, "RunOnTime"
    
    If Cells(1, 4).Value <= Cells(6, 2).Value Then
        Cells(1, 4).Value = Cells(1, 4).Value + 1
        Application.OnTime dTime, "RunOnTime"
    Else
        Cells(1, 4).Value = 0
        Application.OnTime dTime, "RunOnTime"
    End If
End Sub

Hello All,
I am trying to use the OnTime function on some data that is used by a chart.
I have a worksheet that I set Autofilter criteria and I want to apply this criteria every 15 seconds to the data table. I can't seem to find the VBA syntax that will allow for me to do this. Has anyone ever done this, and if so how did you to do it?

I have attached the spreadsheet for reference.

Re: VBA Syntax for Date Range Criteria

Never mind Cytop. If I would have studied your code more closely I would have seen what you were doing. Sometimes I can't see the forest for the trees.

Thanks again for your help.

Re: VBA Syntax for Date Range Criteria

Sorry Cytop, but you have me confused. If the date range cells contain values, then the sQry == sQry & ....... statement is run, however, if the date range cells are empty, then which statement would the code run?

Re: VBA Syntax for Date Range Criteria

Thank you Cytop. Code works great.

Before I consider this issue resolved, how would the code be changed if NO dates were inputted. In this situation I would like the Start and Stop criteria to be "*".

Hello All,
I can't seem to resolve the VBA code syntax for pulling data from an Access query with date range criteria. Here is the code:

'open target query
    sQry = "Select * From qry_WirePull_Destructive_1HeaderSummary Where [DateSaved] Between #05/01/2013# AND #07/17/2013#"
    rst.Open sQry
       
    'set/clear/output to target worksheet
    Set wks = Sheets("WirePull_Destructive_Header")
    On Error Resume Next
    wks.ShowAllData
    wks.Range("A5:AD60000").ClearContents
    wks.Range("A5").CopyFromRecordset rst

What I am trying to do is replace the start and stop dates with Excel named ranges. Does anyone have any ideas?

Thanks

Re: Turn Chart Series 2 Data Labels Off

Thanks Apo. I am very much greatful for your help and teaching me so much about charting and VBA code writing.

Thanks and have a good day.

Re: Turn Chart Series 2 Data Labels Off

That's nice, but what I meant was to change the Text Direction to rotate 270 degrees. What does that code look like?

Re: Turn Chart Series 2 Data Labels Off

Quick question, how do I change the datalabel text direction?

Hello all,
I have a chart on a Chart Sheet and I have a check box on this chart. The problem I am having is that everytime I view the chart the check box keeps moving and resizing. Has anyone else ever had this problem and if so, how did you resolve this?

Thanks