| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MsgBox. problem to continue the macro
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 -- Axel Copy & paste developer |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MsgBox. problem to continue the macro
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 -- Rick (MVP - Excel) "michdenis" wrote in message ... 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" a écrit dans le message de groupe de discussion : ... 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 -- Axel Copy & paste developer |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MsgBox. problem to continue the macro
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" wrote: 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 -- Rick (MVP - Excel) "michdenis" wrote in message ... 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" a écrit dans le message de groupe de discussion : ... 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 -- Axel Copy & paste developer |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MsgBox. problem to continue the macro
It's your preference, i respect that !
|
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MsgBox. problem to continue the macro
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. -- Rick (MVP - Excel) "michdenis" wrote in message ... It's your preference, i respect that ! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MsgBox. problem to continue the macro
| I hate code that is not indented
It's Ok ! I do too... ;-) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| popup msgbox ok continue simple code | Excel Programming | |||
| Problem with Update Link prompt to Continue | Excel Discussion (Misc queries) | |||
| macro to scroll the worksheet and continue to run macro in backgro | Excel Programming | |||
| VB code to continue macro | Excel Discussion (Misc queries) | |||
| Novice - MsgBox Yes/No - Continue if Yes, Close if No | Excel Discussion (Misc queries) | |||
Linear Mode
