Posts by bugs63

    :?:Is There a Way To Create A Command Button, for example a Cancel Button that can take the programmer "in this case me" right to the code page even if the program is running. i am trying to create an administrator type password so that me or any one with a password can access the code.

    Why Is It Cutting Off The Top Of The UserForm it should show the minimize and Maximize as well as resize i am not sure what they are call but they are next to

    the X to close the screen. Any Help would be appreciate it.



    FORM-5.xlsm

    I would like to know if it is possible to add a combo Box an existing Command Button so that when the AddNew cmdButton is press it shows up along with the other Data on the list box provided

    i am sending a copy of the data entry user form that will be used

    I need some help modifying this Excel VBA to run automatically and save as automatically with a current date. Right now it works but you have to use the f5 key for it to run and the current date is not added thank you


    [VBA]Sub ProtectWorksheet()
    On Error Goto ErrorOccured
    Dim pwd1 as String
    pwd1 = InputBox("Please Enter the Password:")
    If pwd1 ="ralph" then
    ActiveSheet.Protect DrawingObjects: =True, Contents: = true, Scenarios: =true, Password: =pwd1
    MsgBox "The Worksheet has now been Locked"
    Exit Sub
    Else
    ErrorOccured:
    MsgBox "Worksheet could not be Locked"
    Exix Sub
    End If
    End Sub
    [/VBA]

    The vba codes i have tryed requires the user to either press yhe F5 key to run the program. Or a series of key to save the file. As far as the what work book to save it under the main work bok is called c:\ Revenue Reports By Month . then a month will be selected and a date put in.

    I would like to save Revenue Report sheets, that are done on a daily bases but need to be saved for a previous date, for example todays date is 08/06/2019
    but the sheets are currently saved manually using the day before 08/05/2019. There is a sheet done every night. It would be nice to save as previous date automatically. Once saved it should be protected. I can attachment if need be so you can see the sheet in question.

    I am looking for an Excel vba to save sheets by a previous date as well Protected by a password with out the need for a macro.
    is this possible. Thank you.

    its a typo my fault this is what i meant to type


    [VBA]


    Code


    Private Sub Worksheet _Change(ByVal Target As Range)
    Dim I As Integer
    for i = To 2 ' The row were the merged cells will be starting in column R to Column X
    if Cells(I,"R").Value <> "" Then
    Cells(I,"R").Value = Date -1 & " " & Time
    Cells(I,"R").NumberFormat = "dddd/mmmm/yyyy h: mm AM/PM"
    End If
    Next
    [/VBA]


    For what ever reason it takes up the entire column, and I would like to confine it to a Row starting in R2 Merged all the way to X2.
    Thank You

    [VBA]


    Code


    Private Sub Worksheet _Change(ByVal Target As Range)
    Dim I As Integer
    for i = 2 ' The row were the merged cells will be starting in column R to Column X
    if Cells(I,"R").Value <> "" Then
    Celles(I,"R").Value = Date -1 & " " & Time
    Cells(I,"R").NumberFormat = "dddd/mmmm/yyyy h: mm AM/PM"
    End If
    Next
    [/VBA]


    For what ever reason it takes up the entire column, and I would like to confine it to a Row starting in R2 Merged all the way to X2.
    Thank You
    .

    Sorry about that I am enclosing the another sample sheet with the actual position of the dash per state, it's not actually a dash it there state logo or map but a dash will be just as good for our project,


    [VBA]


    Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, Cell As Range, strFormat As String
    Dim i As Integer
    On Error GoTo ErrorHandler:
    If Not Intersect(Target, Range("C35:R57")) Is Nothing And Target.Value <> "" Then 'change the range as required
    Application.EnableEvents = False
    For Each Cell In Target
    With Cell
    .Value = WorksheetFunction.Trim(.Value)
    If Len(.Value) > 2 Then
    strFormat = "@@@@" & String(Len(.Value) - 2, "@")
    .Value = UCase(Format(.Value, strFormat))
    i = WorksheetFunction.RandBetween(2, Len(.Value) - 1)
    .Value = Left(.Value, i) & "-" & Right(.Value, Len(.Value) - i)
    Else
    .Value = UCase(.Value)
    End If
    End With
    Next Cell
    Application.EnableEvents = True
    End If
    Exit Sub
    ErrorHandler:
    MsgBox "Error Number:" & Err.Number & vbCrLf & _
    "Error Description:" & Err.Description & vbCrLf & _
    "Error at: " & Cell.Address
    Application.EnableEvents = True
    End Sub
    [/VBA]


    As you can see there are a number of different styles of plates. Personalize plates have no dash in them Some states have 2 letters then numbers. Some do not.

    Thank you so much for all your help on this project Mr.Krishna We really appreciate the additional states added by you. I am enclosing the sample sheet with the actual position of the dash per state, it's not actually a dash it there state logo or map but a dash will be just as good for our project. The code was removed from the sheet to show what the employer wants.

    [INDENT]The Coding Assistants provided by B Rama Krishna was great and is really appreciated.
    i am enclosing a copy of the excel project with some license plate examples. While the dash is random, it dose not comply with some state formats like Connecticut, Texas, Canada, Pennsylvania, New Jersey also its not working on every cell I can not figure it out. More help is needed i am starting to think what my employer is asking is even possible. There is also some data on sheet 2 you will also see that the dash is appearing in the cells were the state abbreviations is placed.[/INDENT]

    [VBA]
    Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, Cell As Range, strFormat As String
    Dim i As Integer
    On Error GoTo ErrorHandler:
    If Not Intersect(Target, Range("C35:R57")) Is Nothing And Target.Value <> "" Then 'change the range as required
    Application.EnableEvents = False

    For Each Cell In Target
    With Cell
    strFormat = "@@@@" & String(Len(.Value) - 2, "@")
    .Value = UCase(Format(.Value, strFormat))
    i = WorksheetFunction.RandBetween(2, Len(.Value) - 1)
    .Value = Left(.Value, i) & "-" & Right(.Value, Len(.Value) - i)
    End With
    Next Cell
    Application.EnableEvents = True
    End If
    Exit Sub
    ErrorHandler:
    MsgBox "Error Number:" & Err.Number & vbCrLf & _
    "Error Description:" & Err.Description & vbCrLf & _
    "Error at: " & Cell.Address
    Application.EnableEvents = True


    [/VBA]