MsgBox. problem to continue the macro

A

Axel

Hi!
Am not able to make the macro continue after an msgbox has been displayed.

This msgbox come as an warning if one cell in the column, contains the same
contents as the textbox 1 is going to insert to the same or another cell in
the column range.

I want the user to be able to do changes in the textboxes on this userform,
after the msgbox has been displayed, but it only work when it's not duplicate
contents in the range

Any suggestions?


Application.ScreenUpdating = False
Dim myStr As String
myStr = TextBox1.Text
If Application.CountIf(v, myStr) > 0 Then
ans = MsgBox("This serialnumber allready exist! Press OK if you want to
continue", vbOKCancel)
If ans = vbCancel Then Exit Sub
Else
Dim iCtr As Integer
iCtr = ComboBox1.Value
Range("B" & CStr(iCtr + 3)) = Me.ComboBox2
Range("C" & CStr(iCtr + 3)) = Me.TextBox1
Range("D" & CStr(iCtr + 3)) = Me.TextBox2
Range("E" & CStr(iCtr + 3)) = Me.TextBox3
Range("F" & CStr(iCtr + 3)) = Me.TextBox4
Range("G" & CStr(iCtr + 3)) = Me.TextBox5
Range("H" & CStr(iCtr + 3)) = Me.Comboday & Me.Combomonth & Me.Comboyear

If TextBox1.Text = "" Then Range("B" & CStr(iCtr + 3)).ClearContents
If TextBox1.Text = "" Then Range("C" & CStr(iCtr + 3)).ClearContents
End If
 
M

michdenis

Hi,

Dim iCtr As Integer

If MsgBox("This serialnumber allready exist! " & _
" Press OK if you want to continue ", vbOKCancel) = vbCancel Then
Exit Sub
Else
iCtr = ComboBox1.Value
Range("B" & CStr(iCtr + 3)) = Me.ComboBox2
Range("C" & CStr(iCtr + 3)) = Me.TextBox1
Range("D" & CStr(iCtr + 3)) = Me.TextBox2
Range("E" & CStr(iCtr + 3)) = Me.TextBox3
Range("F" & CStr(iCtr + 3)) = Me.TextBox4
Range("G" & CStr(iCtr + 3)) = Me.TextBox5
Range("H" & CStr(iCtr + 3)) = Me.Comboday & Me.Combomonth & Me.Comboyear

If TextBox1.Text = "" Then Range("B" & CStr(iCtr + 3)).ClearContents
If TextBox1.Text = "" Then Range("C" & CStr(iCtr + 3)).ClearContents
End If




"Axel" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
Hi!
Am not able to make the macro continue after an msgbox has been displayed.

This msgbox come as an warning if one cell in the column, contains the same
contents as the textbox 1 is going to insert to the same or another cell in
the column range.

I want the user to be able to do changes in the textboxes on this userform,
after the msgbox has been displayed, but it only work when it's not duplicate
contents in the range

Any suggestions?


Application.ScreenUpdating = False
Dim myStr As String
myStr = TextBox1.Text
If Application.CountIf(v, myStr) > 0 Then
ans = MsgBox("This serialnumber allready exist! Press OK if you want to
continue", vbOKCancel)
If ans = vbCancel Then Exit Sub
Else
Dim iCtr As Integer
iCtr = ComboBox1.Value
Range("B" & CStr(iCtr + 3)) = Me.ComboBox2
Range("C" & CStr(iCtr + 3)) = Me.TextBox1
Range("D" & CStr(iCtr + 3)) = Me.TextBox2
Range("E" & CStr(iCtr + 3)) = Me.TextBox3
Range("F" & CStr(iCtr + 3)) = Me.TextBox4
Range("G" & CStr(iCtr + 3)) = Me.TextBox5
Range("H" & CStr(iCtr + 3)) = Me.Comboday & Me.Combomonth & Me.Comboyear

If TextBox1.Text = "" Then Range("B" & CStr(iCtr + 3)).ClearContents
If TextBox1.Text = "" Then Range("C" & CStr(iCtr + 3)).ClearContents
End If
 
R

Rick Rothstein

Close, but I think your code would need to be structured like this instead
(the Else and End If statements where you currently have them would still be
a problem)...

If MsgBox("This serialnumber allready exist! " & _
" Press OK if you want to continue ", vbOKCancel) = _
vbCancel Then Exit Sub
iCtr = ComboBox1.Value
Range("B" & CStr(iCtr + 3)) = Me.ComboBox2
Range("C" & CStr(iCtr + 3)) = Me.TextBox1
Range("D" & CStr(iCtr + 3)) = Me.TextBox2
Range("E" & CStr(iCtr + 3)) = Me.TextBox3
Range("F" & CStr(iCtr + 3)) = Me.TextBox4
Range("G" & CStr(iCtr + 3)) = Me.TextBox5
Range("H" & CStr(iCtr + 3)) = Me.Comboday & Me.Combomonth & Me.Comboyear

If TextBox1.Text = "" Then Range("B" & CStr(iCtr + 3)).ClearContents
If TextBox1.Text = "" Then Range("C" & CStr(iCtr + 3)).ClearContents
 
A

Axel

Thanks guys!
I am very gratful for for help
I did not show the whole code last time, but I made it work like this:

Private Sub BtnOkUsrfmSubs_Click()
Dim v As Range
Set v = Range("C4:C1010")
ActiveSheet.Unprotect Password:="somethings"

If ComboBox1.Value > 1000 Then GoTo line1 Else GoTo Line2
line1:
MsgBox "Only numbers be tween1 og 1000 kan be used"

GoTo Lastline
Line2:

If TextBox1.Text = "" Then GoTo Line3 Else GoTo Line4
Line3:

MsgBox "You have to insert serialnumber"

GoTo Lastline
Line4:

Application.ScreenUpdating = False
Dim iCtr As Integer

Dim myStr As String
myStr = TextBox1.Text

If Application.CountIf(v, myStr) > 0 Then

If MsgBox("This serialnumber allready exist! " & _
" Press OK if you want to continue ", vbOKCancel) = _
vbCancel Then Exit Sub
Else: End If

iCtr = ComboBox1.Value
Range("B" & CStr(iCtr + 3)) = Me.ComboBox2
Range("C" & CStr(iCtr + 3)) = Me.TextBox1
Range("D" & CStr(iCtr + 3)) = Me.TextBox2
Range("E" & CStr(iCtr + 3)) = Me.TextBox3
Range("F" & CStr(iCtr + 3)) = Me.TextBox4
Range("G" & CStr(iCtr + 3)) = Me.TextBox5
Range("H" & CStr(iCtr + 3)) = Me.Comboday & Me.Combomonth & Me.Comboyear
'remove space or tab inserted by user
If TextBox1.Text = "" Then Range("B" & CStr(iCtr + 3)).ClearContents
If TextBox1.Text = "" Then Range("C" & CStr(iCtr + 3)).ClearContents

Sheet1.Select
ActiveSheet.Unprotect Password:="somethings"

For Each c In Range("B4:B10000")
If IsEmpty(c) Then Exit For
Next c

c.Value = "Serialnumber: " & TextBox1.Text & "has been created by user " &
GetUser
c.Offset(0, -1).Value = Date
Unload UsrFrmVarco

ActiveSheet.Protect Password:="somethings", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Lastline:
Sheet2.Select

With Range("B3:K1003")
.Sort _
Key1:=.Cells(1), _
Order1:=xlAscending, _
_
Key2:=.Cells(3), _
Order2:=xlAscending, _
_
Header:=xlYes
End With

ActiveSheet.Protect Password:="somethings", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End Sub

--
Copy & paste developer


Rick Rothstein said:
Close, but I think your code would need to be structured like this instead
(the Else and End If statements where you currently have them would still be
a problem)...

If MsgBox("This serialnumber allready exist! " & _
" Press OK if you want to continue ", vbOKCancel) = _
vbCancel Then Exit Sub
iCtr = ComboBox1.Value
Range("B" & CStr(iCtr + 3)) = Me.ComboBox2
Range("C" & CStr(iCtr + 3)) = Me.TextBox1
Range("D" & CStr(iCtr + 3)) = Me.TextBox2
Range("E" & CStr(iCtr + 3)) = Me.TextBox3
Range("F" & CStr(iCtr + 3)) = Me.TextBox4
Range("G" & CStr(iCtr + 3)) = Me.TextBox5
Range("H" & CStr(iCtr + 3)) = Me.Comboday & Me.Combomonth & Me.Comboyear

If TextBox1.Text = "" Then Range("B" & CStr(iCtr + 3)).ClearContents
If TextBox1.Text = "" Then Range("C" & CStr(iCtr + 3)).ClearContents
Click to expand...
 
R

Rick Rothstein

Actually, I have to apologize to you... I read your code too fast (I hate
code that is not indented)... your code structure would work fine as you
posted it. I'm sorry for any confusion my response to you may have caused.
 

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

unable to get the activate property of the range class 6
answer to Rick 7
help with macro 5
Include/Exclude Holiday - Repost to Programming Section 1
Need to hide date 2
Cell Ref to identify worksheet 1
copy selected cells to new sheet. 12
Help with Macro that contains text and not looks at case 2

Top