About Us

Eduardo

Hi,
I found the answer, thank you so much for your help

"Nyatiaju" wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
EXCEL Capital Letters	hollis	Excel Discussion (Misc queries)	1	November 11th 08 09:03 PM
how to change small letters to capital letters	HOW TO CHANGE	Excel Discussion (Misc queries)	4	May 30th 07 01:12 AM
how do i turn all letters into capital letters?	KeithT	Excel Discussion (Misc queries)	3	May 11th 07 02:13 PM
Capital Letters Only	Simon Jefford	Excel Discussion (Misc queries)	2	February 2nd 06 06:04 PM
Capital Letters	Gaute	Excel Worksheet Functions	4	March 9th 05 09:55 AM