Excel VBA Help To close file paths

[mukker]

As clear as mud? I hope someone can help me with this as i'm relatively new to vba.

Here is the code first:

Option Explicit

Sub ChgInfo()

Dim WS As Worksheet
Dim Search As String
Dim Replacement As String
Dim Prompt As String
Dim Title As String
Dim MatchCase As Boolean

Prompt = "What is the original value you want to replace?"
Title = "Search Value Input"
Search = InputBox(Prompt, Title)

At this point i get a popup asking me for the file path? I have to close it by pressing the 'x' or 'cancel'? Can someone help me with the code to close these? I have attached a screenie (link below)! I have to close 26 of these, it's a real pain.

http://img84.imageshack.us/img84/4497/beforeupdpv4.jpg

Prompt = "What is the replacement value?"
Title = "Search Value Input"
Replacement = InputBox(Prompt, Title)

For Each WS In Worksheets
WS.Cells.Replace What:=Search, Replacement:=Replacement, _
LookAt:=xlPart, MatchCase:=False
Next

End Sub

I hope this has made some sense?

 

[PHV]

What about this ?
Search = [!]VBA.[/!]InputBox(Prompt, Title)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bong]

Maybe I'm dense but I can't tell what you're trying to do. The code you posted explicitly pops 2 prompts: one for text to search for and another for text to replace that text with. Then it looks through all the cells in all the worksheets of the current (active) workbook to find and replace those strings. Why would you do that with a macro?

Anyway, I suspect the spreadsheet is set up to get external data under some circumstances. It won't have anything to do with the posted code. You need to alter the settings on an external data query.

_________________
Bob Rashkin

 

[mukker]

Thanks for the responses. Apologies, the popop is after this bit:

Prompt = "What is the replacement value?"
Title = "Search Value Input"
Replacement = InputBox(Prompt, Title)

Then this happens:

http://img84.imageshack.us/img84/4497/beforeupdpv4.jpg

I know it is looking for external data (that's what the screenie shows), i just want to stop this from appearing using some code.

Apologies if i didn't explain very well.

 

[mukker]

What about this ?
Search = VBA.InputBox(Prompt, Title)

I tried this PHV, no joy?

 

[Bong]

Unless somewhere in your code, you're telling it to refresh the external data (i.e., Query Table), it shouldn't do it on it's own. Is there another Macro that runs on startup?

_________________
Bob Rashkin

 

[mukker]

I have found a way to stop the dialogue box from popping up.

Thanks to all

 

[PHV]

I have found a way to stop the dialogue box from popping up
Any chance you could explain what you did ?

 

[mukker]

I have found a way to stop the dialogue box from popping up
Any chance you could explain what you did ?

Oops apologies!

Here are a few of things i tried:

disabled updating of external links:
ThisWorkbook.UpdateLinks = xlUpdateLinksNever

then changed it back to this afterwards:
ThisWorkbook.UpdateLinks = xlUpdateLinksUserSetting

The one i found to be a bit quicker was this:

Application.DisplayAlerts = False
'code here
Application.DisplayAlerts = True

Hope this helps other people.