Advice to call a code

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,801
Hi,

I have a msgbox where No closes it but i would like when Yes is clicked it will Call say the below to run it


VBA Code:
Private Sub DHLBUTTON_Click()
ActiveWorkbook.FollowHyperlink Address:="http://www.dhl.co.uk/en/express/tracking.html", NewWindow:=True
End Sub
can you advise the correct way to wright it please
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,928
VBA Code:
Private Sub PressMyButton()

If MsgBox("Please press Yes or No!", vbYesNo, "Make your choice") = vbYes Then
    DHLBUTTON_Click
End If

End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,801
Afternoon,

This is the code in use.

VBA Code:
Private Sub ListBox1_Click()
  Dim answer As Integer

  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
  MsgBox "OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE"
If answer = vbYes Then
    ???????
Else
    Unload DatabaseNameSearch
End If
End Sub
Where you see the ????? this is where i need to Call Private Sub Worksheet_BeforeDoubleClick

Just cant seem to do anything today
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,928
I don't think you can call Private Sub Worksheet_BeforeDoubleClick - this is an event code and only gets called when you double click.

However, whatever code you are calling in that Private Sub Worksheet_BeforeDoubleClick, just call that where you have your "?????"
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,801
OK
So if i use the same code but have it run without the double click,.
If this was code to run etc without double click how would it run when YES is selected.


I have this now but after i select YES the userform closes & thats it.
The code added doesnt do anything.

I cant see why this is so hard.

VBA Code:
Private Sub ListBox1_Click()
  Dim answer As Integer

  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
  MsgBox "OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE"
If answer = vbYes Then
    If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub
    Cancel = True
    Database.LoadData Me, Target.Row
Else
    Unload DatabaseNameSearch
End If
End Sub
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,928
Because in your code, you are saying
VBA Code:
If answer = vbYes Then
... but you haven't set the value of answer.

Try a small modification:

VBA Code:
Private Sub ListBox1_Click()
  Dim answer As Integer

  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
  answer=MsgBox()"OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE")
If answer = vbYes Then
    If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub
    Cancel = True
    Database.LoadData Me, Target.Row
Else
    Unload DatabaseNameSearch
End If
End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,801
Syntax error with this

VBA Code:
answer=MsgBox[COLOR=rgb(184, 49, 47)]()[/COLOR]"OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE")
Now when run i see RTE 424Object Required

This in yellow
Code:
If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,801
OK
Plan B

This works fine but a slight edit & it will be ok.

Code:
Private Sub ListBox1_Click()
  Dim answer As Integer

  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
  answer = MsgBox("OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE")
   If answer = vbYes Then
    Database.Show

Else
    Unload DatabaseNameSearch
End If
End Sub
Lets say i select Tom Jones in the listBox.
I then see the MsgBox
I select YES
Then the DATABASE opens up.

So far so good.

On this userform is a comBobox called ComboBoxCustomersNames


Now if you manually select a name from this ComboBox it will do what i require.

SO
The edit would be for the ListBox selection to then select the same name in the userform ComboBox.

Did i explain ok ?

Thanks
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,801
If its easier for you.
The customers name is also selected in column A
Then the userform DATABASE opens.

So remember the selection in Column A & use that to select from the comboBox ?
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,928
Syntax error with this

VBA Code:
answer=MsgBox[COLOR=rgb(184, 49, 47)]()[/COLOR]"OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE")
Now when run i see RTE 424Object Required

This in yellow
Code:
If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then
for some reason there was an additional ")" in the line I adjusted, sorry! Should have read:

VBA Code:
answer=MsgBox("OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE")
 

Forum statistics

Threads
1,081,496
Messages
5,359,042
Members
400,517
Latest member
RC8401

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top