Clicky

Thank you all for helping me!

I have this code:


If Not Intersect(Target, Range("D1:D600")) Is Nothing Then
        For Each change In Target.Cells
            If change.Text <> "N/A = Not Applicable" And Target.Text <> "" Then
                Application.EnableEvents = False
                'Get data from user
                'Do While message = ""
                 message = InputBox("Enter Justification for Column " & Mid(change.Address, 2, 1), "Mandatory data entry")
                'Loop
                '-- insert data in target sheet
                Worksheets("JUSTIFICATION").Range(change.Address) = message
                '-- insert comment in current sheet
                Cells(change.Row, 4).Hyperlinks.Add Cells(change.Row, 4), "", "JUSTIFICATION!" & change.Address, , change.Text
                Application.EnableEvents = True
            End If
        Next change
    End If

And as you can see I am trying to make it Mandatory.  But it does not work, so I need help!

Thank you very much!

asked 12/13/2011 09:08

Amour22015's gravatar image

Amour22015 ♦♦


18 Answers:
Do you have an example sheet?
Do you get an error?
link

answered

Jupiler78's gravatar image

Jupiler78

Jupiler78


Thank you.

No I do not get any errors.

And no I do not have an example sheet.  

All I am looking for is that this is Mandatory?

When I try:

Do While message = ""

Loop

For some reason it is not working.




Thank you!
link

answered 2011-12-14 at 04:17:15

Amour22015's gravatar image

Amour22015

I have tested your loop and it works normally for me.

1:
2:
3:
Do While message = ""
 message = InputBox("Enter Justification for Column test" , "Mandatory data entry")
 Loop

If you put that code into a macro what does it do? For me it repeats until I give some input.
link

answered 2011-12-14 at 05:21:55

Jupiler78's gravatar image

Jupiler78

Jupiler78

Thank you, you are right for some reason it was not working before.

But I also have this for when a change is made:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    If Target.Address = "" And Target.SubAddress <> "" Then
        Do While Range(Target.SubAddress) = ""
        Range(Target.SubAddress) = InputBox("Update Justification", "Selected JUSTIFICATION", Range(Target.SubAddress))
        Loop
        Sh.Activate
    End If
End Sub

And this Do While does not work, maybe I have it in the wrong area?

Thank you for helping me!
link

answered 2011-12-14 at 05:26:59

Amour22015's gravatar image

Amour22015

Amour22015,

You test in the If statement for Target.SubAddress <> ""  and in the Do While you test for that to be empty.
I think you made a little mistake.
link

answered 2011-12-14 at 05:43:39

Jupiler78's gravatar image

Jupiler78

Jupiler78

Thank you!

I tried:
If Target.Address = "" And Target.SubAddress <> "" Then
        Do While Chgmess = ""
        Chgmess = InputBox("Update Justification", "Selected JUSTIFICATION", Range(Target.SubAddress))
        Range(Target.SubAddress) = InputBox("Update Justification", "Selected JUSTIFICATION", Range(Target.SubAddress))
        Loop
        Sh.Activate
    End If

But it goes into a loop

Need some help?

Thank you!
link

answered 2011-12-14 at 05:45:54

Amour22015's gravatar image

Amour22015

try following:
1:
2:
3:
4:
Do While Chgmess = ""
        Chgmess = InputBox("Update Justification", "Selected JUSTIFICATION", Target.SubAddress)
        Target.SubAddress = InputBox("Update Justification", "Selected JUSTIFICATION", Target.SubAddress)
        Loop
link

answered 2011-12-14 at 07:31:34

Jupiler78's gravatar image

Jupiler78

Jupiler78

Thank you!

That just made it come up with:

JUSTIFICATION!$D$24

in the messageBox and looped?

I am going to keep trying different things until it works.

But I still am going to need your help until this is done?

Thank you for helping me!
link

answered 2011-12-14 at 08:37:19

Amour22015's gravatar image

Amour22015

There should come up 2 inputboxes after each other.
The looping depends on what you fill in in the  first one.
The second one updates the shape.
I don't understand it why you need it 2 times, because you ask 2 times the same in the inputbox.
link

answered 2011-12-14 at 09:35:45

Jupiler78's gravatar image

Jupiler78

Jupiler78


I don't want it to loop twice.  The only reason for:
Chgmess = InputBox("Update Justification", "Selected JUSTIFICATION", Target.SubAddress)

is to do:
Do While Chgmess = " "

That is most likely where the problem is?

So I don't really need:
Chgmess = InputBox("Update Justification", "Selected JUSTIFICATION", Target.SubAddress)


So instead of:
If Target.Address = "" And Target.SubAddress <> "" Then
        Do While Chgmess = ""
        Chgmess = InputBox("Update Justification", "Selected JUSTIFICATION", Range(Target.SubAddress))
        Range(Target.SubAddress) = InputBox("Update Justification", "Selected JUSTIFICATION", Range(Target.SubAddress))
        Loop
        Sh.Activate
    End If



I need something like: But I need help with the Do While statement?

If Target.Address = "" And Target.SubAddress <> "" Then
        Do While  ?????????  = ""
        Range(Target.SubAddress) = InputBox("Update Justification", "Selected JUSTIFICATION", Range(Target.SubAddress))
        Loop
        Sh.Activate
    End If


Thank you!  
link

answered 2011-12-14 at 09:39:42

Amour22015's gravatar image

Amour22015

try:

1:
2:
3:
4:
5:
6:
If Target.Address = "" And Target.SubAddress <> "" Then
        Do While  Target.SubAddress = ""
        Target.SubAddress = InputBox("Update Justification", "Selected JUSTIFICATION", Target.SubAddress)
        Loop
        Sh.Activate
    End If
link

answered 2011-12-14 at 11:48:43

Jupiler78's gravatar image

Jupiler78

Jupiler78


Using what you said earlier:
You test in the If statement for Target.SubAddress <> ""  and in the Do While you test for that to be empty.
I think you made a little mistake.

Looks like the samething mistake.

Tried and it does not bring anything up.  This did not work?

Thank you!
link

answered 2011-12-14 at 11:50:34

Amour22015's gravatar image

Amour22015

Sorry, my mistake. :)

Of course it won't work, first it tests if subaddress is not empty and then the loop that checks wether is it empty.

So, or you should make the first test that it checks if subaddress is empty, then you get following code, but then I don't know why there is the test for address itself.
1:
2:
3:
4:
5:
6:
If Target.Address = "" And Target.SubAddress="" Then
        Do While  Target.SubAddress = ""
        Target.SubAddress = InputBox("Update Justification", "Selected JUSTIFICATION", Target.SubAddress)
        Loop
        Sh.Activate
End If


Or the loop should be about address and not subaddress:
1:
2:
3:
4:
5:
6:
If Target.Address = "" And Target.SubAddress <> "" Then
        Do While  Target.Address = ""
        Target.Address = InputBox("Update Justification", "Selected JUSTIFICATION", Target.SubAddress)
        Loop
        Sh.Activate
    End If
link

answered 2011-12-14 at 13:48:48

Jupiler78's gravatar image

Jupiler78

Jupiler78


Thank you again, but both not working.

Code 1:

Does not go into messagebox?

Code 2:
Watchout goes into a continues loop and only Task Manager can end the spreadsheet.

Have to keep trying

Thank you!
link

answered 2011-12-14 at 13:56:26

Amour22015's gravatar image

Amour22015

Can't you sent me the excel-file that has the problem?
Or a simple version of it?

Code1: that is possible when Target.Address <> ""

can you try both there, I have put msgbox inside of them to know the values.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
MsgBox "Address1:" & target.Address
MsgBox "SubAddress1:" & target.SubAddress
If target.Address = "" And target.SubAddress = "" Then
        Do While target.SubAddress = ""
        target.SubAddress = InputBox("Update Justification", "Selected JUSTIFICATION", target.SubAddress)
        MsgBox "SubAddress:" & target.SubAddress
        Loop
        Sh.Activate
End If[/code

And this for case 2 : 
[code]MsgBox "Address1:" & target.Address
MsgBox "SubAddress1:" & target.SubAddress
If target.Address = "" And target.SubAddress <> "" Then
        Do While target.Address = ""
        target.Address = InputBox("Update Justification", "Selected JUSTIFICATION", target.SubAddress)
        MsgBox "Address2:" & target.Address
        Loop
        Sh.Activate
End If
link

answered 2011-12-14 at 14:52:36

Jupiler78's gravatar image

Jupiler78

Jupiler78

Thank you for helping me!

I think I know why this is coded like this?,  The reason is so that the user can DELETE the text in the hidden worksheet BEFORE deleting the link to that cell.  Otherwise the hidden worksheet would be full of unwanted text.  So, logically this would not be a good idea.

I am going to have to repost a different solution to get what I want

Thank you very much and Merry Christmas
link

answered 2011-12-14 at 22:32:32

Amour22015's gravatar image

Amour22015

Great job and thanks!
link

answered 2011-12-15 at 14:49:10

Amour22015's gravatar image

Amour22015

Glad I could help. :)
link

answered 2011-12-15 at 14:50:42

Jupiler78's gravatar image

Jupiler78

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×16
×19
×36
×83

Asked: 12/13/2011 09:08

Seen: 356 times

Last updated: 12/15/2011 06:50