Posts by edleets

Re: Password Input with limited Tries

Thanks Logit. It will work and I will have it for future needs. After reading some posts related to the issue and thinking about a simple way of doing it, a way came to my mind: way not to repeat the initialization of the form till it meets the numbers of attempt? It happens too fast that the user do not notice it and have the counter in the initialization of the userform. Well, I tried, and It Worked!!. Here it is the code in case someone else may use it. I appreciate all of your helps!.

This is the OK command button

Private Sub CmdOK_Click()
    
    Dim msb As String
    Dim username As String
    username = Application.username
    msb = frmMyPass.TextBox1.Value
    
    If msb <> Hoja8.Range("B28") Then
        iCounter = iCounter + 1
        MsgBox (username & ": " & "Tienes " & 3 - iCounter & " intentos")
        TextBox1.Value = ""
       [COLOR=#FF0000] U[B]serForm_Initialize[/B][/COLOR]
        Exit Sub
    Else
        Unload Me
        Sheets("MENU").Activate
        Range("I2").Select
    End If
    
End Sub

and this is the Initialization of the userform

Private Sub UserForm_Initialize()
TextBox1.SetFocus
    If iCounter >= 3 Then
        Unload Me
        ThisWorkbook.Close savechanges:=False
    End If
End Sub

Re: Password Input with limited Tries

Roy
There is a difference between protecting from other users to OPEN it (and NOT use it at all) and "workbook protection" where it is limiting changes in the structure of teh workbook but can use it and navigate in all sheets.

Re: Password Input with limited Tries

Thanks Roy, but it is too complicated to understand it. I do not want many users or changing password. My module is simple, 1 user, 1 password. The limitation on number of tries entering the password is just to protect it from other users to opening it. Can you simplify it to my needs?

Thanks

Hi,
I have a code at "This Workbook" that shows a userform that ask for a password once a file is open. If the password is not correct, the user cannot actívate the workbook but can indefinetdly continues trying the password unless he presses the "cancel" button that closes the workbook. However, I would like the code to limit the number of times a user can enter the password to 3 times and close the workbook after the 3rd try. I would like also the messagebox that tells the password is wrong to let the user know it has x remaining tries.

Thanks

Private Sub CmdOK_Click()
    
    Dim msb As String
    Dim username As String
    
    username = Application.username
    msb = frmMyPass.TextBox1.Value
   
   
    If msb <> "testing" Then
        MsgBox (username & ": " & "wrong password")
        TextBox1.Value = ""
        TextBox1.SetFocus
    Else
        Unload Me
        Sheets("MENU").Activate
        Range("I2").Select
    End If
    
End Sub

Re: Waiting userform not asting all time

Thanks Roy, I will try it. The only thing is that the page will cover the entire screen and it will work, but it looks more professional to just use a little message window (or userform) but will try it.

Thanks

Re: Waiting userform not asting all time

Hope it is right now

Re: Waiting userform not asting all time

Hi Pike, sorry I forgot. It is with quotes now.

Thanks

Hello
I have a code that reads more than 100,000 records to do something. Since this takes about 1 minute, I have added a userform to show while code is running and unload it when done. However, the userform only shows for a few seconds and then it goes off and the screen is blank (with the little circle going around) until the work is done. I want the userform to stay and goes off until the work is done. Is something wrong with the placement of the userforms show and unload statements? I have tried to put the Unload at the very end just before the "end Sub" statement but still the same problem. Is it a problem of VBA coding or speed or memory of the hardware? Here is the example:

Sub myfirstcode()
 
All DIM variables goes here
 
ActiveWorkbook.PrecisionAsDisplayed = False
   
[COLOR=#ff0000] ufWait.Show[/COLOR]
   
 Application.ScreenUpdating = False
   
[COLOR=#ff0000] Unload ufWait[/COLOR]
   
  Application.EnableEvents = False
  Application.DisplayAlerts = False
 
CODE GOES HERE
 
ActiveWorkbook.PrecisionAsDisplayed = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True


NOTE: I have also tried placing the Unload ufWait here and still the same problem
   
End Sub

Re: A Challenge Hourly Report

Thanks
Yes, it now runs but I got an endless loop. Can you check the code I sent you yesterday? It is strange becuase with the sample data set (6,000 records or more) it runs perfect, but with the entire data set (104K records) it gets stuck. Thanks

Re: A Challenge Hourly Report

Hi Carim
Thank, I am done with my project, I finally found the way to fix all of my programing problems. I used a sample of the data to run it and it ran perfect. However, now that I want to run it with the entire set of data (104,226 records) the subroutine "InsertarLineas" is giving me an "overflow" error. I even declared the lastrow variable to "Long" but I am still getting the error. Is there any other variable that is affecting the overflow? I even test it declaring the variable "X" to "Long" too and still the same overflow error. Why the subroutine "HourlyReport", if desactivating calling the "InsertarLineas" subroutine, is not giving the same error if going thru the same amount of data set?

I cannot load the file with all set of data because of size limit, so I am attaching it with the sample size. Thanks

Re: A Challenge Hourly Report

Hola Carim

Energia looks perfecto but if manually you add up the total energía of the segment of missing records (ejemplo: only 3 records instead of 4) you will notice it is not what comes out in the report. You can intentionally erase some records (either 1, 2, or 3 of the segment of 4 record/hr) you will notice the total reported if not the sum of the remaining records of the same segment, it is taking the records of next segment. My latest versión can be tweaked, but if I do not resolve the problema of the change of "idClient", it will not work (will probably give me and endless loop). But I will keep trying.

Saludes, y gracias por su ayuda..

Re: A Challenge Hourly Report

Hello
Thanks, that was a Smart approach and I think you are getting very close to the final solution :wink: I said this because the macro is taking the records of next segment of hrs instead of the missing records. I think the variable D you added will not always test x+3 because of the missing record. I think your latest versión would be wonderful if adding the row of the missing record, maybe a combination of your latest versión and mine. I am glad for all your help!

Re: A Challenge Hourly Report

Hello Carim

I found a way to overcome the missing records!!. It is creating another macro (subroutine) to first go over all records and find the missing records (if difference in time is 15 min) and inserting lines for every missing record, and then we can run the report for summarizing the hourly data for every 4 records since now we will be sure there will always be 4 records for every segment of 1 hr.

The only little problem I have now is when the plant (column B "idclient") changes to a new Plant (see row # 2974) because the previous Plant already got their 31 days and new Plant will start on day 1 where the conditions of 15 min will not longer be valid. How to do this? maybe using a While...end loop? Can you help me to add it to the macro that insert lines?

Thanks .

Re: A Challenge Hourly Report

Hello Carim

The report is based on an hourly segments. Each segment has to have (ideally) 4 records (every 15 min). If 1 or more records are missing, I need to get the total of the remaining records. Example: if 2 records are missing, get the total of the others 2 records and continue with the next segment, BUT do not take the missing records out of the next segment. Hope this illustrate better my needs. Thanks for your help.

Re: A Challenge Hourly Report

Hi Carim
I have tried to use this version but I do not understand why the values are 0... can you please review and help?

Re: A Challenge Hourly Report

Quote from Carim;788037

Hello Edmundo,

Congratulations on your macro ... !!!

You are an Expert ... !!! :wink:

As a starting point ... attached is a small modification ...

Hope this will help

:smile:

Display More

Thanks Carim, it is fantastic! your are fast!. Just one thing... the data for the missing record is not included in the report. For example, if there needs to be 4 records withing 1 hr (15 min each) and one record is missing, the macro need to only account for the rest records, in this case 3 records instead of 4 and add the answer in the report. In your code, it is not reported at all. The report is missing the 15,110.61 KWh missing for the 3 records from 4:00 to 4:45. In other words, it always need to read, add the KWh and include it in the report no matter how many records (1,2,3, or 4 maximum). It will only be not reported in the case the 4 records are missing.

Thanks

Re: A Challenge Hourly Report

Hi Claim
I worked the macro to do the report. However, it is without considering the missing records. I intentionally deleted 1 record to check if the macro considers it but it is fixed to read and summarize every 4 records independently of the time period. Could you please help me on fixing the macro to read 4 only the records within the time of 1 hr as I explained in the first post? Also, my macro is not completing the last 2 records at the end of the report. Thanks

Re: A Challenge Hourly Report

Thanks Carim, but that is a formula in the worksheet, I need the macro to do that and get the hourly report.

Thanks

Hi Logit
Here is the problem I have that you may help me to create the report I need... My knowledge of VBA is as a beginner and think this report requires much more experience and knowledge of programming. Here is the situation: I have a list of Plant Generators and a list of data info regarding the energy (KWh) delivered to the system. The info, each lecture, is sent every 15 minutes everyday. It means that in a month, let's say January, the monthly report for every generator needs to be 2,976 records (31 days x 24 hrs/day x 4 times/hr), there are 40 generators, so the list is huge (around 119k records), but I have just attached the file with a little portion as sample.

The report for every generator needs to be minimized in an hourly version adding all the kwh delivered every 15 minutes of the hour. It will be an easy task IF the records stay perfect (4 records every hour) since it would only be necessary to have a loop that adds the records every 4 times; however, sometimes a record is missing and it is when it gets complicated because you cannot add the first record of the next hour but instead to add only the 3 records within that hour and so for. At the end, a report of exception (missing records) need to be created.

I am attaching the file where I have done the programing but as a total version (not hourly), IF possible, could you help me on this? Any kind of help either programming or idea of how to do it, will be appreciated. Thanks

Re: Copied Form doens't move

Sorry, still above the 2K bytes... I will check it myself, it maybe an application procedure that was not set back as default... I will be requiring your help in something else I will post during the week, thanks anyway.