On Jan 23, 7:35*pm, Eduardo wrote:
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

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