userform Field forcing Capital Letters - Thank you

E

Eduardo

Hi all, and thanks in advance
I have created an userform to control Projects advance, the problem I am
running into is that when a project # is entered i.e. ES1001, it goes to the
summary sheet and create a tab for this project, then I can transfer amounts
from one month to another and delete projects. When deleting I have a
problem, I bring the deleting form and you enter the project number, it
checks if the project can be deleted and then delete the tab and is supposed
to delete the movement from the summary. The tab is being deleted but not the
information in the summary. I discover that the problem is that if the
project was entered in Capital letters ES1001 and in the delete userform I
enter es1001, it pull all the information delete the tab but not the
information in the summary
Is there any way for that specific fields in the userform everything being
entered as Capital or maybe when going to delete information in the summary
to validate the Cap and Lower case.
Here is the code I am using that by the way is a mix of different codes find
in this wonderful comunity. Thank you
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim res As Variant
Dim rng As Range
Dim i As Long
Dim rng1 As Range
Dim i1 As Long


Set ws = Worksheets("Projects")


'find first empty row in database

With ws
iRow = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

'check for a project number
If Trim(Me.TxtProjectCode.Value) = "" Then
Me.TxtProjectCode.SetFocus
MsgBox "Please enter a project number"
Exit Sub
End If

res = Application.Match(Me.TxtProjectCode.Value, _
Worksheets("Projects").Range("e:e"), 0)
If IsError(res) Then
If IsNumeric(Me.TxtProjectCode.Value) Then
res = Application.Match(CDbl(Me.TxtProjectCode.Value), _
Worksheets("Projects").Range("e:e"), 0)
End If
End If

If IsNumeric(res) Then


'fill out information
Me.TxtClient.Value = .Cells(res, "C").Value
Me.TxtProjectname.Value = .Cells(res, "d").Value
Me.CboBusiness.Value = .Cells(res, "b").Value





popUp = MsgBox("Are you sure you want to delete Project" & " - " &
Me.TxtProjectCode.Value, vbYesNo + vbQuestion, "Project Deletion")
If popUp = vbYes Then

' check if Project code has not revenue applied

Worksheets(Me.TxtProjectCode.Value).Visible = True
Worksheets(Me.TxtProjectCode.Value).Select

Set rng = ActiveSheet.Range(Cells(1, "BA"), Cells(Rows.Count,
"BA").End(xlUp))

With rng
For i = .Rows.Count To 1 Step -1
If .Cells(i) = "RR" Then


popUp = MsgBox("Project has Recognized Revenue applied and cannot be
deleted", vbMsgBoxRtlReading, "Recognized Revenue Applied")

Worksheets(Me.TxtProjectCode.Value).Visible = False

'clear the data
Me.CboBusiness.Value = ""
Me.TxtProjectname.Value = ""
Me.TxtClient.Value = ""
Me.TxtProjectCode.Value = ""

Exit Sub

Else

End If

Next i



' Delete Project Sheet
Worksheets(Me.TxtProjectCode.Value).Delete

End With




' To start statement to delete rows
Worksheets("Projects").Select


Set rng1 = ActiveSheet.Range(Cells(1, "E"), Cells(Rows.Count, "E").End(xlUp))

'Work backwards from bottom to top when deleting rows
'This will delete the row if cell value = Project Code
Me.TxtProjectCode.SetFocus

With rng1

For i1 = .Rows.Count To 1 Step -1
If .Cells(i1) = res Then
.Cells(i1).EntireRow.Delete
End If
Next i1

End With


End If


'clear the data
Me.CboBusiness.Value = ""
Me.TxtProjectname.Value = ""
Me.TxtClient.Value = ""
Me.TxtProjectCode.Value = ""

Me.TxtProjectCode.SetFocus


' To finish code if Project Tab doesn't exist

Else
popUp = MsgBox("Project Code never entered before",
vbMsgBoxRtlReading, "Project Not in Database")

Me.TxtProjectCode.Value = ""
Exit Sub
End If

Worksheets("Menu").Select
End With


End Sub
 
N

Nyatiaju

Hi all, and thanks in advance
I have created an userform to control Projects advance, the problem I am
running into is that when a project # is entered i.e. ES1001, it goes to the
summary sheet and create a tab for this project, then I can transfer amounts
from one month to another and delete projects. When deleting I have a
problem, I bring the deleting form and you enter the project number, it
checks if the project can be deleted and then delete the tab and is supposed
to delete the movement from the summary. The tab is being deleted but notthe
information in the summary. I discover that the problem is that if the
project was entered in Capital letters ES1001 and in the delete userform I
enter es1001, it pull all the information delete the tab but not the
information in the summary
Is there any way for that specific fields in the userform everything being
entered as Capital or maybe when going to delete information in the summary
to validate the Cap and Lower case.
Here is the code I am using that by the way is a mix of different codes find
in this wonderful comunity. Thank you
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim res As Variant
Dim rng As Range
Dim i As Long
Dim rng1 As Range
Dim i1 As Long

Set ws = Worksheets("Projects")

'find first empty row in database

With ws
iRow = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

'check for a project number
If Trim(Me.TxtProjectCode.Value) = "" Then
Me.TxtProjectCode.SetFocus
MsgBox "Please enter a project number"
Exit Sub
End If

res = Application.Match(Me.TxtProjectCode.Value, _
Worksheets("Projects").Range("e:e"), 0)
If IsError(res) Then
If IsNumeric(Me.TxtProjectCode.Value) Then
res = Application.Match(CDbl(Me.TxtProjectCode.Value), _
Worksheets("Projects").Range("e:e"), 0)
End If
End If

If IsNumeric(res) Then

    'fill out information
    Me.TxtClient.Value = .Cells(res, "C").Value
    Me.TxtProjectname.Value = .Cells(res, "d").Value
    Me.CboBusiness.Value = .Cells(res, "b").Value

     popUp = MsgBox("Are you sure you want to delete Project" & "- " &
Me.TxtProjectCode.Value, vbYesNo + vbQuestion, "Project Deletion")
  If popUp = vbYes Then

  ' check if Project code has not revenue applied

  Worksheets(Me.TxtProjectCode.Value).Visible = True
  Worksheets(Me.TxtProjectCode.Value).Select

  Set rng = ActiveSheet.Range(Cells(1, "BA"), Cells(Rows.Count,
"BA").End(xlUp))

With rng
    For i = .Rows.Count To 1 Step -1
        If .Cells(i) = "RR" Then

    popUp = MsgBox("Project has Recognized Revenue applied and cannot be
deleted", vbMsgBoxRtlReading, "Recognized Revenue Applied")

    Worksheets(Me.TxtProjectCode.Value).Visible = False

    'clear the data
       Me.CboBusiness.Value = ""
        Me.TxtProjectname.Value = ""
        Me.TxtClient.Value = ""
        Me.TxtProjectCode.Value = ""

    Exit Sub

          Else

         End If

         Next i

' Delete Project Sheet
 Worksheets(Me.TxtProjectCode.Value).Delete

End With

    ' To start statement to delete rows
 Worksheets("Projects").Select

Set rng1 = ActiveSheet.Range(Cells(1, "E"), Cells(Rows.Count, "E").End(xlUp))

'Work backwards from bottom to top when deleting rows
'This will delete the row if cell value = Project Code
Me.TxtProjectCode.SetFocus

With rng1

    For i1 = .Rows.Count To 1 Step -1
        If .Cells(i1) = res Then
            .Cells(i1).EntireRow.Delete
        End If
    Next i1

End With

End If

        'clear the data
        Me.CboBusiness.Value = ""
        Me.TxtProjectname.Value = ""
        Me.TxtClient.Value = ""
        Me.TxtProjectCode.Value = ""

        Me.TxtProjectCode.SetFocus

     ' To finish code if Project Tab doesn't exist

     Else
       popUp = MsgBox("Project Code never entered before",
vbMsgBoxRtlReading, "Project Not in Database")

          Me.TxtProjectCode.Value = ""
               Exit Sub
         End If

  Worksheets("Menu").Select
  End With

End Sub
Click to expand...

Hello,

If you are using text box to enter project number, you may use below
code which converts textbox entry to upper case:

Private Sub TextBox1_Change()
Me.TextBox1.Value = UCase(Me.TextBox1.Value)

End Sub

Thanks
 
E

Eduardo

Hi, and thank you for your response.
Capital letters works Fantastic, however the code is not working and not
deleting the rows that match the project code, any ideas??
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dave Peterson you help me with an userform could you help me again 4
Userform enter a field checking other two fields - Thank you 2
Userform - validation data. Please help needed 2
Userform enter a field checking other two in database 1
userform duplicate control not working in my code. Thank you 13
Compare col and match then copy and paste 2
find closest match and copy to sheet1 5
Some macros not working on shared workbook 8

Top