Variables in a Popup prompt

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

Msgbox below works fine, returns a capital letter for j and an ASCII char for i.
The timed Popup works fine with prompt shown, want another timed Popup to display what the message box does and do away with the msgbox.
i and j are declared variable refering to two cells on the worksheet.

Possible?

I have tried to put the j and the i in the prompt section of this timed popup but it rejects .Popup "j & " for " & i" as well as .Popup j & " for " & i


MsgBox j & " for " & i

CreateObject("WScript.Shell").Popup "Select another Capital Letter in K1 to
convert to ASCII", _
2, "ASCII Select"

(The CreateObject... text will probably wrap)

Thanks,
Howard
 
Howard said:
Msgbox below works fine, returns a capital letter for j and an ASCII
char for i.
Click to expand...

I assume that's supposed to be "ASCII value".
The timed Popup works fine with prompt shown, want another
timed Popup to display what the message box does and do away with the
msgbox. i and j are declared variable refering to two cells on the
worksheet.

Possible?

I have tried to put the j and the i in the prompt section of this timed
popup but it rejects .Popup "j & " for " & i" as well as .Popup j & "
for " & i
Click to expand...

Let's look at those, first...

"j & " for " & i"
string + keyword + string = syntax error

j & " for " & i
variable + string + variable = *should* be working

Indeed, this works for me:

j = "A"
i = 65
CreateObject("WScript.Shell").Popup j & " for " & i, 2, "ASCII Select"
MsgBox j & " for " & i

CreateObject("WScript.Shell").Popup "Select another Capital Letter in K1
to convert to ASCII", _
2, "ASCII Select"

(The CreateObject... text will probably wrap)
Click to expand...

You might try explicitly assigning the two variables to a string:

Dim tmp As String
tmp = j & " for " & i
CreateObject("WScript.Shell").Popup tmp, 2, "ASCII Select"
 
Msgbox below works fine, returns a capital letter for j and an ASCII char for i.

The timed Popup works fine with prompt shown, want another timed Popup to display what the message box does and do away with the msgbox.

i and j are declared variable refering to two cells on the worksheet.



Possible?



I have tried to put the j and the i in the prompt section of this timed popup but it rejects .Popup "j & " for " & i" as well as .Popup j & " for " & i





MsgBox j & " for " & i



CreateObject("WScript.Shell").Popup "Select another Capital Letter in K1 to

convert to ASCII", _

2, "ASCII Select"



(The CreateObject... text will probably wrap)



Thanks,

Howard
Click to expand...

Thanks Auric,
I could have sworn I tried this ....Popup j & " for " & i...
and it did not work...??? Does now!

I went with this and it works fine also.

Dim tmp As String
tmp = j & " for " & i
CreateObject("WScript.Shell").Popup tmp, 2, "ASCII Select"

However, if I try to run them both in the same macro the first one works fine but the second does indeed does popup but does not time out, just sits there until you hit OK or X. If I comment either one out the other works.

Have you any idea whats going on there?? One or the other but not both.

(yes ASCII value not char)
Thanks again for the help.
Howard
 
Howard said:
Thanks Auric,
I could have sworn I tried this ....Popup j & " for " & i...
and it did not work...??? Does now!

I went with this and it works fine also.

Dim tmp As String
tmp = j & " for " & i
CreateObject("WScript.Shell").Popup tmp, 2, "ASCII Select"

However, if I try to run them both in the same macro the first one works
fine but the second does indeed does popup but does not time out, just
sits there until you hit OK or X. If I comment either one out the other
works.

Have you any idea whats going on there?? One or the other but not both.
Click to expand...

I have no idea.

Since you're using a scripting object, I recommend popping over to a VBScript
group and asking them. microsoft.public.scripting.vbscript looks fairly busy;
ask there. (Let them know you're calling from VBA, in case it makes a
difference.)
 
Msgbox below works fine, returns a capital letter for j and an ASCII char for i.

The timed Popup works fine with prompt shown, want another timed Popup to display what the message box does and do away with the msgbox.

i and j are declared variable refering to two cells on the worksheet.



Possible?



I have tried to put the j and the i in the prompt section of this timed popup but it rejects .Popup "j & " for " & i" as well as .Popup j & " for " & i





MsgBox j & " for " & i



CreateObject("WScript.Shell").Popup "Select another Capital Letter in K1 to

convert to ASCII", _

2, "ASCII Select"



(The CreateObject... text will probably wrap)



Thanks,

Howard
Click to expand...

Thanks for the advice. I'm getting in a bit above my head on scripting objects and at the same time I'm finding the msgbox and the timed popup don't seem to add a great deal of value to the process. I'll most likely dump them.

I do appreciate you time and thoughts.

Regards,
Howard
 
Howard said:
Thanks for the advice. I'm getting in a bit above my head on scripting
objects and at the same time I'm finding the msgbox and the timed popup
don't seem to add a great deal of value to the process. I'll most
likely dump them.

I do appreciate you time and thoughts.
Click to expand...

No prob.

One last thought.. If you really want the timed msgbox, you could easily
make your own. Create a form with a label and an OK button, and add this
code to the form:

Public x As Single

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Activate()
x = Timer
Do
DoEvents
If Timer > (x + 2!) Then Unload Me: Exit Sub
Loop
End Sub

Private Sub UserForm_Initialize()
Label1.Caption = some_public_variable
End Sub

The message would have to come from a public variable set by your code
(which I've called "some_public_variable" here). You'd use this like so:

Public some_public_variable As String

Sub foo()
j = "A"
i = 65
some_public_variable = j & " for " & i
UserForm1.Show
While UserForm1.Visible
DoEvents
Wend
End Sub

The downside of this is that the CPU usage will spike to 100% for those 2
seconds. (DoEvents will allow other things to happen so the machine doesn't
just lock completely up, but still.)

There are other ways of doing this (see Application.Wait, for example), but
you'll need to experiment to see if you like any of them. (A regular VB
timer control would solve all these problems, but I can't see how to add
one to the VBA form.)
 
Back
Top